Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables and In one table I have currency field and transaction amount. Other table has Currency and the conversion Rate. There I want to get the monthly average rate and convert all amount to LKR currency. Currently five currencies avalable,(LKR, USD, EUR, GBP, AUD). But it is better to give a way to convert any currency. Please help on this.
Eg:-
Transaction Table:
Date Currency Amount
2019/01/10 USD 1500
2019/01/10 LKR 2500
2019/01/10 AUD 550
2019/01/11 USD 100
2019/01/11 EUR 2500
2019/01/12 AUD 550
Currency ratio Table:
Date Currency Rate
2019/01/12 AUD 125.2
2019/01/10 USD 179.7
The best bet will be to create a mapping table for the FX rates with CurrencyMonthKey
Map_FX:
MAPPING LOAD
Currency & Date(Date, 'YYMM') as CurrencyMonthKey,
Rate
FROM [your FX table];
Then create the same key in your transaction table, e.g.
Currency & Date(Date, 'YYMM') as CurrencyMonthKey,
And then ApplyMap on the FX rate and then the converted values in two preceding loads:
LOAD
*,
Amount * FXRate as [LKR Amount]
;
LOAD
*,
ApplyMap('Map_FX', CurrencyMonthKey, -1) as FXRate
;
LOAD
Currency & Date(Date, 'YYMM') as CurrencyMonthKey,
Obviously you could combine the bottom two preceding loads, but I'm just splitting out for clarity.
You would need to deal with what happens when there is a missing rate. I've put in a -1 so it highlights the error, you may want to put in 0 or 1, or something else.
Once you have LKR Amount in the table you could then have a data island which will allow you to covert to any other currency in the front end, if required?
Hope that helps.
Hi, Thanks for the reply.
In Currency ratio table, There is no any rate for LKR since it is the main currency. So how to create an expression to get the amount in LKR?
Also i am getting the monthly average like below.
Avg({<Transaction_Date=>})Total <Month> (Currency_Rate)
Hi, I cannot modify the table and how should I give the if statement there?
Hi,
You would not have to modify the source table, as you could just append to it in Sense. The simpler route will probably be the IF statement.
That will read like this:
if(Currency = ' LKR', 1, ApplyMap('Map_FX', CurrencyMonthKey, -1)) as FXRate,
This way any LKR values will be multiplied by 1 and retain the same value as before.
Steve