How do I convert results to an X-Y table?

[claatu]

I have query results that look like this:

Risk      Age     Total
High     gt30    16
High     gt60      3
High     gt90   184
Low      gt30      4
Low      gt60    42
Low      gt90     84

I want it to be in this X-Y table format where X is Age and Y is Risk:

And note that there is no result for Med risk, but that has to be accounted for in the end formatted table as zeroes.

How the Splunk do I get that to happen?

Alternatively, I can get the results in this form, with zeroes for Med:

But then, how do you convert that into the desired X-Y table format?

[kamlesh_vaghela · Accepted solution]

@claatu

Can you please try this?

YOUR_SEARCH
| table Risk Age Total
| append [|makeresults| eval Risk="High,Med,Low",Risk=split(Risk,","),Age="gt30,gt60,gt90",Age=split(Age,","),order="1,3,2",order=split(order,",") | mvexpand Risk] | fillnull value=0 Total 
| chart sum(Total) over Risk by Age |eval priority = if (Risk="High",1,if(Risk="Med",2,if(Risk="Low",3,4)))| sort priority | fields - priority

My Sample search:

| makeresults 
| eval data="High gt30 16|High gt60 3|High gt90 184|Low gt30 4|Low gt60 42|Low gt90 84|Med gt90 8" , data=split(data,"|") 
| mvexpand data 
| eval Risk=mvindex(split(data," "),0),Age=mvindex(split(data," "),1),Total=mvindex(split(data," "),2) 
| table Risk Age Total
| append [|makeresults| eval Risk="High,Med,Low",Risk=split(Risk,","),Age="gt30,gt60,gt90",Age=split(Age,","),order="1,3,2",order=split(order,",") | mvexpand Risk] | fillnull value=0 Total 
| chart sum(Total) over Risk by Age |eval priority = if (Risk="High",1,if(Risk="Med",2,if(Risk="Low",3,4)))| sort priority | fields - priority

Thanks

[claatu]

Wow, that works! Is there a way to make it sort like this:
High
Med
Low

instead of
High
Low
Med

?

[claatu]

Figured it out.

| eval sort_field=case(Risk="High",1, Risk="Med",2, Risk="Low",3) | sort sort_field | fields Risk gt30 gt60 gt90

[Vijeta]

Yes you can create a new field and assign numbers to it at the end of your code, add this -

eval priority = if (risk="High",1,if(risk="Medieum",2,if(risk="Low",3,4)))| sort priority | fields - priority

[Vijeta]

If you have this data
Risk Age Total
High gt30 16
High gt60 3
High gt90 184
Low gt30 4
Low gt60 42
Low gt90 84

you can have query as chart sum(total) by risk age

[claatu]

This method does not produce a zero result for the Med column.

[vinaykata]

can you post the query you're using now

[claatu]

It's really long and involved so I don't want to post that here. But if you want to experiment you can just create a lookup table per the results I first mentioned (or the alternative results), and do an |inputlookup on it and go from there.