Hi, I have a few columns of data and I would like to generate a pivot table that is similar to the one in Excel.
As an example:
Date/City/Day/Response ----------------- 09-01/New York/Monday/Yes 09-05/New York/Friday/Yes 09-01/Los Angeles/Tuesday/Yes 09-07/Chicago/Monday/No 08-28/San Francisco/Wednesday/No 08-23/Dallas/Sunday/Yes
How can I organize/sort these data so it reports the following for all days:
City/Yes/No Chicago/0 /1 Dallas/1 /0 Los Angeles/1 /0 New York/2 /0 San Francisco/0 /1
And reports the following for Monday only:
City/Yes/No Chicago/0 /1 New York/1 /0
This is a fairly simple search, provided that you extract the fields City and Response from a set of events, where each event is one line of your data:
... | chart count by City Response
The default lists the fields in alphabetical order. What's the syntax if I want to list them in a non-alphabetical order? i.e.,
San Francisco/0 /1
Los Angeles/1 /0
New York/2 /0
Yes, with difficulty. Create an auxiliary field with the eval command and a case statement like: ... | eval order = case(City == "SF", 1, ...) | sort order | fields - order
Thanks again Stephen for your help. There is one draw back from your solution. I want to sort for a specific range of dates and the data often does not contain "San Francisco". The code given above will create a "San Francisco" field even though no applicable data is available.
If you have a CSV file that enumerates all cities, than before the "| chart" you can add "| inputlookup append=t