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
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*
This should work
| inputlookup lookup.csv | untable Date col2 col3
unfortunately this command only accepts 3 arguments, but I will need more soon.
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*
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.
The "search" for the conversion would be
| inputlookup lookup.csv | ...
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.
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