Use multiple inputs to a lookup table to return a xref value

[markwymer]

Hi all,

I have a lookup table of Currency exchange rates per day per currency code e.g. (cutdown!)

Date,USD,JPY,GBP
2017-05-12,1.0876,123.82,0.84588
2017-05-11,1.086,123.69,0.84485
2017-05-10,1.0882,123.84,0.83985

My logged events contain fields for the Currency Codes (could be one of many currency codes but only one Currency Code per event) and a value for the transaction for example:-

CurrCode=JPY
value=200.00

I have created a new field from the timestamp to create a lookup date using:-

convert timeformat="%Y-%m-%d" ctime(_time) AS LU_Date

So, my query is - how can I use the CurrCode value and the LU_Date value to return the exchange rate? My ultimate goal is to then multiply the value in the event by the exchange rate to get a 'base' value across all currencies.

Thanks for any help.
Mark.

[dineshraj9]

Modify the lookup content this way -

Date,CurrCode,Value
2017-05-12,USD,1.0876
2017-05-12,JPY,123.82
2017-05-12,GBP,0.84588
2017-05-11,USD,1.086
2017-05-11,JPY,123.69
2017-05-11,GBP,0.84485
2017-05-10,USD,1.0882
2017-05-10,JPY,123.84
2017-05-10,GBP,0.83985

Then you can get value from the lookup this way -

<your search> | lookup Date as LU_Date,CurrCode as CurrCode OUTPUT Value