Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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?
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.
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.
Hi,
Can I know if I should rename the Currency in main table as From_Currency and then calculate ABCost_EUR??
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...
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?
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.
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.