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.
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