Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser22
Creator II
Creator II

Currency Conversion from EUR to all other currencies

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:

Maintable.PNG

CurrencyTable:

currency_table.PNG

ExpectedOutput

Expected.PNG

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. 

 

Labels (3)
1 Solution

Accepted Solutions
henrikalmen
Specialist
Specialist

Your file abc.xlsx has currencies for every day, but you want to load only the latest day's data into CurrencyTable. If you are sure that the file has data for the specific date that you want, you could add a filter in the style of where Curr_timestamp>=today() so that the loaded table in Qlik Sense only holds the values you are interested in.

The mapping tables make sense, they can be used to convert currency (using the ApplyMap() function) when loading MainTable. (Note that the "Currecytable_destination" load is missing the mapping prefix, and "as %target_currency" is irrelevant since it doesn't matter what names the fields in a mapping table has.)

When the mapping tables are loaded you could drop CurrencyTable, unless you need it to display rates in the applications. You don't need it for backend calculations.

What should I do if I don't have a date column in my main table?
You don't need a date column in MainTable for the conversion if you convert all values using your mapping tables, i.e. with the latest day's currency rates. But if you want to convert using different currencie values based on dates, that's a different task.

What to do if the main table has a date column?
Same answer as previous question.

 

View solution in original post

19 Replies
henrikalmen
Specialist
Specialist

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?

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
qlikuser22
Creator II
Creator II
Author

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
Specialist
Specialist

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
Creator II
Creator II
Author

Hi,

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

henrikalmen
Specialist
Specialist

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
Creator II
Creator II
Author

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
Specialist
Specialist

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
Creator II
Creator II
Author

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.