Hi,
is it possible to use a column header for a lookup?
Let's say that we have a csv like this:
Date | A | B
01.01.2014| 5 | 2
02.01.2014| 5 | 2
03.01.2014| 5 | 2
Fields in the event:
Date=02.01.2014
Key=A
Now I need the output from the csv:
output=5
For
Date=02.01.2014
Key=B
the output would be 2 and so on...
Thanks in advance
Heinz
Thanks for your comments.
The number of keys will increase in the future and the number of values can vary.
I tried out the transpose command, but this created confusing (not linear lookup) tables.
Try this
your base search giving Date and Key fields | join Date, Key [|inputlookup yourlookupfile.csv | untable Date Key Value ] | table Date, Key, Value
I don't think that this is possible because there is no "key" field. The headers should only be used as key.
I think an easier option is to find a way to convert the crosstable into a list and use this as a lookup afterwards.
Thanks everbody for your input!
transpose the rows to column values and try with props & transforms.
Key,Date,Value1,Value2
A,Date,5,2
A,Date,6,2
B,Date,1,3
B,Date,6,2
etc..Use ,
|lookup Date as Date Key as Key OUTPUTFIELD Value1,Value2
Is the list of values for field 'Key' static and total no of values smaller?