creating table with different stats command

[Chandras11]

Hi All,

Is it possible to combile the stats from different queries in a single table or string. If I can create a string then table can be generated as:-

| makeresults
 | eval data="Product Revenue,420000;  Services Revenue,210000;  Fixed Costs,-170000; Variable Costs,-140000;"
 | makemv delim=";" data  | mvexpand data  | eval data=split(data,",")  | eval name=mvindex(data,0)  | eval value=mvindex(data,1)
 | table name value

I am trying to generate a Waterfall diagram with the results from different queries:-
Query1 | stats count as Product Revenue
Query2 | stats count as Services Revenue
Query3 | stats count as Fixed Costs
Query4 | stats count as Variable Costs

However, I am not certain, how can I execute the 4 queries altogether and then combine the stats count as a string "Key1,Value1;Key2,Value2;Key3,Value3;Key4,Value4;").

[somesoni2 · Accepted solution]

Try this

| makeresults
  | eval data="Product Revenue,420000;  Services Revenue,210000;  Fixed Costs,-170000; Variable Costs,-140000;"
  | makemv delim=";" data  | mvexpand data  | eval data=split(data,",")  | eval name=mvindex(data,0)  | eval value=mvindex(data,1)
  | table name value
| eval temp=1 
| chart count over temp by name | fields - temp

OR (if it's not count but sum of value field)

| makeresults
  | eval data="Product Revenue,420000;  Services Revenue,210000;  Fixed Costs,-170000; Variable Costs,-140000;"
  | makemv delim=";" data  | mvexpand data  | eval data=split(data,",")  | eval name=mvindex(data,0)  | eval value=mvindex(data,1)
  | table name value
| eval temp=1 
| chart sum(value) over temp by name | fields - temp

[Chandras11]

Hi, Thanks for the reply. My question is for generating the string similar to data from 4 different queries. Here I put values in "data" only for example and my code is working fine. However, I really don't know, how can I execute 4 queries altogether, gather the stats count result and then create a single sting out of these 4 results and their name (key-value pair).

[somesoni2]

You can probably use append/appendcols command to get results from your 4 searches into one search. Then you can format the output to get a single string with count value. Like this

Query1 | stats count as "Product Revenue" | appendcols 
[search Query2 | stats count as "Services Revenue"] | appendcols
[search Query3 | stats count as "Fixed Costs"] | appendcols
[search Query4 | stats count as Variable Costs] 
| eval temp=1 | untable temp Key Value
| eval FinalOutput=Key."=".Value 
| stats values(FinalOutput) as FinalOutput delim="," | nomv FinalOutput

[Chandras11]

Thanks a lot. I was trying to use the append command but appendcols is a better one. 🙂