Dashboards & Visualizations

Create a Pivot Table

wyang6
Path Finder

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

Thanks!

Tags (2)
0 Karma
1 Solution

Stephen_Sorkin
Splunk Employee
Splunk Employee

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

View solution in original post

Stephen_Sorkin
Splunk Employee
Splunk Employee

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

Stephen_Sorkin
Splunk Employee
Splunk Employee

If you have a CSV file that enumerates all cities, than before the "| chart" you can add "| inputlookup append=t .csv" where .csv is in etc/apps//lookups.

0 Karma

wyang6
Path Finder

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.

0 Karma

Stephen_Sorkin
Splunk Employee
Splunk Employee

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

0 Karma

wyang6
Path Finder

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.,

     City/Yes/No

San Francisco/0 /1
Chicago/0 /1
Dallas/1 /0
Los Angeles/1 /0
New York/2 /0

Thanks again!

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...