Currency Conversion from EUR to all other currencies

[qlikuser22]

Hi,

I am stuck in performing currency conversion between two tables. 

Example:

MainTable:

Load

Country,

Currency,

..

..

..

ABCost,

ABCurr, // values in EUR,INR

BCCost,

BCCurr, // values only in EUR

CDCost,

CDCurr // values in EUR,INR,GBP,USD

from aaa.xlsx

CurrencyConversionTable:

Load 

CurrencyDate,

From_currency,

target_currency,

exchnage_rate

from bbb.xlsx;

Maintable:

CurrencyTable:

ExpectedOutput

Now I want to convert ABCost, BCCost, CDCost into EUR and create a new column "TotalCost_EUR" (which is the sum of ABCost,BCCost,CDCost). 

Also in QLIK visualization, I would like to see the TotalCost in all currencies (GBP,INR,USD,etc). 

I really don't know how to proceed. Please do help!

Thanks a lot in advance. 

[henrikalmen]

There are multiple ways of doing this. I would probably use mapping tables in load script, something like the following.

Before loading main table, create mapping table for conversion:

EURmap: mapping load From_Currency, exchange_rate where Target_Currency='EUR' resident CurrencyTable;

And when loading the main table, apply the mapping as a calculation. For ABCost to become ABCost in EUR you would do this:

ABCost * applymap('EURmap', From_Currency, 1) as ABCost_EUR

If the currency is already EUR, the '1' in the applymap function will make sure the original value is multiplied by 1 (i.e. the new value will be the same as the original value) but that will also occur if the currency is missing in currencytable.

[MayilVahanan]

Hi

if you are maintaining the currency based on currency date, in that case, do you want to load only the latest currency info from CurrencyTable? or do you have any date column in the Main Table also?

[qlikuser22]

Hi. 

I don't have data column in main table and I need to load the latest currency from currency table. I have only four currencies in my main table(GBP;INR;USD;EUR). 

But, my currency table has all the currencies. 

[henrikalmen]

Then you should create the mapping table with only values for the latest CurrencyDate for each Currency. You could for example first create a temporary table, something like this:

curtmp: load Currency&max(CurrencyDate) as %curkey resident CurrencyTable;

And then add to the mapping table creation:
where exists(%curkey, Currency&CurrencyDate)

Dont forget to drop curtmp; afterwards.

[qlikuser22]

Hi,

Can I know if I should rename the Currency in main table as From_Currency and then calculate ABCost_EUR?? 

[henrikalmen]

There's no need for that, I'm not sure why you're asking. Perhaps reading about how ApplyMap() works will clarify some things: https://help.qlik.com/en-US/sense/February2023/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFun...

[qlikuser22]

Hi,

I am asking this question, because if I have the dimension from_currency in mapping table and Currency in main_table. 

EURmap:

mapping load

from_currency,

exchange_rate

resident currencytable

where target_currency ='EUR';

main_table:

load

currency,

..

..

..

applymap('EURmap',from_currency,1) as ABCostEUR; 

from abc.xlsx;

it says from_currency not found. 

Any help? 

[henrikalmen]

In your first example, the field is named From_Currency - but now you wrote from_currency (with no capital letters). Capital letters matter in Qlik's field names, it's not like in SQL where it doesn't matter if you write FRom_CURRency or whatever. In Qlik it really matters.

[qlikuser22]

Really sorry for creating this confusion. I have used exactly the same way. 

Example:

EURmap:

mapping load

from_currency,

exchange_rate

resident currencytable

where target_currency ='EUR';

main_table:

load

currency,

..

..

..

applymap('EURmap',from_currency,1) as ABCostEUR; 

from abc.xlsx;

even in my currencytable it is from_currency only. 

Still I get the error as from_currency not found.