Refine your search:

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!

asked 07 Sep '10, 19:09

wyang6's gravatar image

wyang6
593212
accept rate: 100%


One Answer:

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
link

answered 07 Sep '10, 21:43

Stephen%20Sorkin's gravatar image

Stephen Sorkin ♦
9.7k511
accept rate: 52%

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!

(08 Oct '10, 20:30) wyang6

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

(09 Oct '10, 04:53) Stephen Sorkin ♦

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.

(27 Oct '10, 14:58) wyang6

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

(27 Oct '10, 18:26) Stephen Sorkin ♦
Post your answer
toggle preview

Follow this question

Log In to enable email subscriptions

RSS:

Answers

Answers + Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×886
×280

Asked: 07 Sep '10, 19:09

Seen: 2,873 times

Last updated: 07 Sep '10, 21:43

Copyright © 2005-2014 Splunk Inc. All rights reserved.