Hi
This is my data :
I want to group result by two fields like that :
I follow the instructions on this topic link text , but I did not get the fields grouped as I want. They are grouped but I don't have the count for each row.
Can anyone help me?
Thanks
Give this a try
your base search giving fields Location, Book and Count
| stats sum(Count) as Count by Location Book
| stats list(Book) as Book list(Count) as Count by Location
Your data actually IS grouped the way you want. You just want to report it in such a way that the Location doesn't appear. So, here's one way you can mask the RealLocation with a display "location" by checking to see if the RealLocation is the same as the prior record, using the autoregress function.
This part just generates some test data-
| makeresults | eval mydata = "NYC,book1,3 NYC,book2,5 NYC,book3,3 Boston,book1,32 Boston,book2,51 Boston,book3,32 Dallas,book1,13 Dallas,book3,13 Dallas,book2,15"
|makemv mydata| mvexpand mydata |makemv delim="," mydata
| eval Location=mvindex(mydata,0), Book=mvindex(mydata,1), Count=mvindex(mydata,2)
| table Location, Book, Count
This part sorts it and masks the RealLocation
| sort 0 Location, Book
| autoregress Location
| rename Location as RealLocation
| eval Location=if(RealLocation==Location_p1,"-",RealLocation)
| table Location Book Count RealLocation
With these results
Location Book Count RealLocation
Boston book1 32 Boston
- book2 51 Boston
- book3 32 Boston
Dallas book1 13 Dallas
- book2 15 Dallas
- book3 13 Dallas
NYC book1 3 NYC
- book2 5 NYC
- book3 3 NYC
Hi,
Thanks for your help.
This worked for me :
| stats sum(Count) as Count by Location Book
| stats list(Book) as Book list(Count) as Count by Location
However, you might want to consider this format instead -
|chart sum(Count) over Book by Location
...which gives this result
Book Boston Dallas NYC
book1 32 13 3
book2 51 15 5
book3 32 13 3
Give this a try
your base search giving fields Location, Book and Count
| stats sum(Count) as Count by Location Book
| stats list(Book) as Book list(Count) as Count by Location
Hi,
I tried your command but. The data are listed as I want but the count column is empty.
Do you know why?
I made a mistake in my command.
It worked. thanks for your help
Hi,
try the following.
your-base-search | stats count by location, book
It tried this command but I have the same result