Currency conversion issue

[dushan1987]

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

[stevedark]

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.

[dushan1987]

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?

[dushan1987]

Also i am getting the monthly average like below.

Avg({<Transaction_Date=>})Total <Month> (Currency_Rate)

[stevedark]

You would either need to create an FX rate of 1 for each month in your FX table, so LKR is not affected by FX, or put an IF statement to check for it before doing the ApplyMap.

[dushan1987]

Hi, I cannot modify the table and how should I give the if statement there?

[stevedark]

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