Use event date to with an uploaded CSV

[ModupeSebapole]

events are loaded with different currency from different countries and we are trying to have a view converting the currency into one currency.  We have uploaded CSV with average exchange rate per month and would like to display a table using event date and use the rate from CSV, as the rates should be calculated as per the current rates and it should always change as we load new month rates  

[ITWhisperer]

Please can you share your events as well and which fields you have already extracted?

[ModupeSebapole]

index="rpa_aria"
|eval Start=strptime(start,"%Y-%m-%d %H:%M:%S")
|eval Stop=strptime(end,"%Y-%m-%d %H:%M:%S")
|eval Instance = process_name + " - " + country
|convert dur2sec(Stop) dur2sec(Start)
|eval TimeDiff=(Stop - Start)
|eval TAT=tostring(TimeDiff, "duration")
|stats avg(TimeDiff) as AVTAT, sum(heartbeat) as Vol, sum(passed) as STP, sum(amount) as Val by Instance
|lookup Bench.csv Instance AS Instance OUTPUT BenchSecs
|lookup Bench.csv Instance AS Instance OUTPUT Owner
|lookup Bench.csv Instance AS Instance OUTPUT Salary
|eval BenchTAT = BenchSecs
|eval BotTATpT = (AVTAT/Vol)
|eval MSal=(Salary/12)
|eval Delta=(BenchTAT-BotTATpT)
|eval HB2B = (BenchTAT*Vol/3600)
|eval Capacity = HB2B/((44*4)*0.886)
|eval Delta% = 100 * (BenchSecs-BotTATpT) / BenchTAT
|eval SalRed = (Capacity * MSal)*12
|eval LCY=case(Instance like "%Eswatini%","SZL",Instance like "%Lesotho%","LSL", Instance like "%Namibia%","NAD", Instance like "%Botswana%","BWP",Instance like "%Ghana%","GHS",Instance like "%Uganda%","UGX",Instance like "%Tanzania%","TZS",Instance like "%Malawi%","MWK",Instance like "%South Africa%","ZAR",Instance like "%Angola%","AOA",Instance like "%Congo%","CDF",Instance like "%Kenya%","KES",Instance like "%Mozambique%","MZN",Instance like "%Nigeria%","NGN",Instance like "%Zambia%","ZMW",Instance like "%Zimbabwe%","ZWL")
|lookup FX.csv LCY AS LCY OUTPUT Rate
|eval ZAR = if(Instance="Recon Bizwize File Transfer - Botswana",300000, SalRed * Rate)
|eval ZARVal = (Val * Rate)
|eval BotTAT = BotTATpT
|eval STP% = (STP/Vol)*100
|table Instance, LCY, Rate, ZAR |sort - ZAR
|addcoltotals ZAR

[ITWhisperer]

If I understand correctly, you want the current month to be used find the right column from the csv? Try something like this:

| lookup FX.csv LCY
| eval month=strftime(now(),"%Y/%m")
| foreach 20*
    [| eval Rate=if("<<FIELD>>"=month,'<<FIELD>>',Rate)]
| fields - 20*