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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...