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!

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...