How to convert a crosstable into a list format to use as a lookup file?

[HeinzWaescher]

Hi,

I would like to convert a crosstable into a list.

Date      | A | B 
01.01.2014| 5 | 2
02.01.2014| 5 | 2
03.01.2014| 8 | 9

The output should be:

col1       |col2| col3
01.01.2014 | A | 5
01.01.2014 | B | 2
02.01.2014 | A | 5
02.01.2014 | B | 2
03.01.2014 | A | 8
03.01.2014 | B | 9

Is there an easy way to do this? I tried out the transpose command, but without success.

BR

Heinz

[somesoni2]

This should work

| inputlookup lookup.csv | untable Date col2 col3

[HeinzWaescher]

unfortunately this command only accepts 3 arguments, but I will need more soon.

[tom_frotscher · Accepted solution]

I think this might not be the best solution. But maybe this is a good starting point:

| inputlookup answers.csv | stats values(A) as col3 by Date | eval col2="A" | append [| inputlookup answers.csv | stats values(B) as col3 by Date | eval col2="B"] | rename Date as col1 | sort col1 | table col*

[HeinzWaescher]

Thanks, this works! But I think it could be easier to prepare the flat list with excel & pivot before importing, when there will be more columns. This search needs an update every time a new column is added.

[HeinzWaescher]

The "search" for the conversion would be

| inputlookup lookup.csv | ...

[HeinzWaescher]

Hi Patrick,

there is no search 🙂 My goal is just convert an uploaded csv, which looks like the above example (a crosstable). I want to use it as a lookup file, therefore I need to flatten it. I can do it everytime with excel before uploading, but a Splunk solution would be much more comfortable.

[ppablo]

Hi @HeinzWaescher

It'll be helpful if you can paste your current search query that people can work with and tweak to get the result you want 🙂

Patrick