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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...