Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
dushan1987
New Contributor II

Currency conversion issue

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

6 Replies
MVP & Luminary
MVP & Luminary

Re: Currency conversion issue

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
New Contributor II

Re: Currency conversion issue

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
New Contributor II

Re: Currency conversion issue

Also i am getting the monthly average like below.

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

MVP & Luminary
MVP & Luminary

Re: Currency conversion issue

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
New Contributor II

Re: Currency conversion issue

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

MVP & Luminary
MVP & Luminary

Re: Currency conversion issue

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