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. 

[qlikuser22]

I used this

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

[henrikalmen]

Is your field named from_currency or From_Currency? Qlik sees those variants as different fields.

Also, you have a semi-colon before "from", you can't have that.

The field names in the resulting mapping table doesn't matter

EURmap:
mapping load from_currency as A, exchange_rate as B
resident currencytable //is the table actually named currencytable?
where target_currency ='EUR';

main_table:
load
   currency,
   ...,
   applymap('EURmap',from_currency,1) as ABCostEUR 
from abc.xlsx;

For this to work, the field "from_currency" that holds the name of the currency you are converting from must exist in the file abc.xlsx

[MayilVahanan]

Hi

In your main table, its not "From_Currency", its Currency.

 applymap('EURmap',currency,1) as ABCostEUR 

[qlikuser22]

Thanks a lot @MayilVahanan  & @henrikalmen.

I wrote three applymap statements in main table now,

ABCost * applymap('EURmap',ABCurr,1) as ABCostEUR,

BCCost* applymap('EURmap',BCCurr,1) as BCCost_EUR,

CDCost* applymap('EURmap',CDCurr,1) as CDCost_EUR

I am using ABcurr, BCCurr, CDCurr because I need to convert those currencies into EUR.

Thanks! 🙂

[qlikuser22]

Hi @henrikalmen ,

I have an updated table now with date field in both the tables.

I want to find the latest exchange rate based on the date field. 

CurrencyTable:

Curr_date,

from_currency,

target_currency,

exchange_rate

from def.xlsx;

Main_trable:

Currency,

ABCost,

ABCurr,

Creation_date,

..

..

..

from abc.xlsx;

I tried to load the max(Curr_date). Didn't seem to work. Not sure if I am writing the load statement properly. 

Thanks for helping out!

[henrikalmen]

Did you see my suggestion here? (I'm trying to link to one of my responses in this thread where I wrote about how to get only the latest currency rate, if it doesn't work just look for it in all the comments.)

[qlikuser22]

HI again,

I think I am getting too confused with this loading latest date concept. Let me be very clear to get some help from you.

I have a

CurrencyTable:

Curr_timestamp,

from_currency,

target_currency,

daily_rate

from abc.xlsx

where match(from_currency,'GBP','INR','USD','EUR')

and match(target_currency,'GBP','INR','USD','EUR');

Now my CurrencyTable has daily data and my script should run everyday and it has to pick only the latest currency daily_rate based on the date in Curr_timestamp dimension.
I need to find the latest daily rate and apply that in my mapping tables.

I have two mapping tables:

amt_map_GBP: // this mapping is used to convert all the cost into GBP
mapping load
from_currency,
daily_rate
resident CurrencyTable
where target_currency = 'GBP';

Currencytable_destination: // this mapping is used to convert the final KPI's from GBP to other currencies(INR,USD,EUR)
load
target_currency as  %target_currency,
daily_rate
resident CurrencyTable
where from_currency = 'GBP';

I need help to understand 
1. What should I do if I don't have a date column in my main table?

2. What to do if the main table has a date column?

Thanks for your patience. 

Hoping for the help.! 

[henrikalmen · Accepted solution]

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.

[qlikuser22]

Awesome thanks a lot. I have done like your first suggestion and it works perfect. 🙂

[qlikuser22]

Hello,

I have modified my script with a date column. Created a composite key and mapped that again my main table to apply the exchange rate. Now my currency table has the exchange rates only from Jan 2022. But my main table has data from Jan 2019. 

So I need to apply the currency conversion with the exchange rates from Jan 2022 and for the previous years I need to apply with a constant value. Any help here please? 

How to write this in a script?

thanks again!