Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody!
I have a report that needs to be updated on a monthly basis. The problem is that there are different currencies and I have to convert all values to EUR using a monthly exchange rate.
I attached a sample of the data and the exchange rates list. What I need is a way to divide the values in the column Price by the respective values in the column Currency_Rate and have the result in a new column named Price_EUR. Of course the formula needs to pick up the right currency and the right monthly exchange rate.
I have no idea how to do this and would appreciate any help! Thank you!
- Mikel
Hi Mikel,
Please have a look at the below query. Hope this solves your problem.
Map_CurrMonth_ExchRate:
Mapping LOAD
Upper(Currency_Name)&Date(Currency_Month,'YYYYMM') as Key,
Currency_Rate
FROM Rates.xlsx
(ooxml, embedded labels, table is Rates);
Sample:
LOAD Product,
Country,
Document_Date,
Currency,
Quantity,
Price,
Price/ApplyMap('Map_CurrMonth_ExchRate',Upper(Currency)&Date(Document_Date,'YYYYMM')) as Price_EUR
FROM Sample.xlsx
(ooxml, embedded labels, table is Sample);
Thanks
Dinesh
Hi Mikel,
Please have a look at the below query. Hope this solves your problem.
Map_CurrMonth_ExchRate:
Mapping LOAD
Upper(Currency_Name)&Date(Currency_Month,'YYYYMM') as Key,
Currency_Rate
FROM Rates.xlsx
(ooxml, embedded labels, table is Rates);
Sample:
LOAD Product,
Country,
Document_Date,
Currency,
Quantity,
Price,
Price/ApplyMap('Map_CurrMonth_ExchRate',Upper(Currency)&Date(Document_Date,'YYYYMM')) as Price_EUR
FROM Sample.xlsx
(ooxml, embedded labels, table is Sample);
Thanks
Dinesh
Hello Mikel,
Trust that you are doing good!
Please use below given draft code snippet to solve your problem.
Map_Currency_Rate:
Mapping Load
Currency_Name & '|' & Date(Currency_Month,'YYYYMM') AS CurrencyKey,
Currency_Rate
FROM
Rates.xlsx
(ooxml, embedded labels, table is Rates);
Data:
LOAD Product,
Country,
Document_Date,
Currency,
Quantity,
Price,
Price / ApplyMap('Map_Currency_Rate', Currency & '|' & Date(Document_Date,'YYYYMM')) AS Price_EUR
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sample);
I have attached demo application herewith.
Regards!
Rahul
Hello Dinesh and Rahul,
Thank you very much for your help guys!
@Dinesh I tested your code this morning and it seems to work very well with my sample data. I still have to test it with the full dataset during next week, but I have a very good feeling about this.
@Rahul Unfortunately, I could not run the attached QVF file, but since you and Dinesh use a similar approach I have no doubt that your code will work perfectly as well.
May I have one more question? The Mapping Load is new to me and I am not entirely sure how it works. How does Qlik Sense know that Price should be divided by Currency_Rate if we do not specify this in the expression that generates the Price_EUR value? Sure, the Mapping Load now only includes Key and Currency_Rate but what would happen if there were other numerical values in the exchange rates list, e.g. Previous_Rate, Forecasted_Rate, etc. Or do we only load the Key + the field we need for the calculation?
Thank you very much again!
Best,
Mikel
Hello Mikel,
Thank you so much for the appreciation!
Mapping load is an alternative to Join in situations where you need to look up a single value from another table is to use mapping instead. This can save you from loading unnecessary data that slows down calculations and potentially can create calculation errors, as joins can change the number of records in the tables.
A mapping table consists of two columns: a comparison field (input) and a mapping value field (output). In above example we have a data table with transactions, and need to know the price of transaction post conversion into Euro.
The mapping table, which we name Map_Currency_Rate, is defined two columns - CurrencyKey (Used to link Mapping table with other table) & Currency_Rate (Which will replace the key value).
In the next step is to apply the mapping, by using the ApplyMap function when loading the data table. The third parameter of the ApplyMap function is used to define what to return when avalue is not found in the mapping table.
//Syntax of ApplyMap
ApplyMap('MappingTableName', MappingColumn, DefaultValue) AS ColumnName
As mentioned earlier, mapping table consists of two fields only. If you have multiple rates such as Current_Rate, Previous_Rate & Forecasted_Rate then you can create multiple mapping tables for each rate type; else use joins.
The Join prefix in Qlik Sense is a powerful way of combining several data tables in the data model. One disadvantage is that the combined tables can become large and create performance problems.
Hope this cleared all your queries.
Regards!
Rahul
Thank you very much! Great explanation!
Hi!
I'm trying to achieve something similar. My main data does not have date field, but only Currency and Price. My exchange rates file is basically the same.
Taking the sample data here as an example, I want to divide Price by the latest Currency_Rate available. Is this possible with a script expression?
Thanks!
Hello Steve,
If CurrencyRates table contains unique currency codes then please use below given sample script.
Map_Currency_Rate:
Mapping Load
Currency_Code,
Currency_Rate
FROM
CurrencyRates.xlsx
(ooxml, embedded labels, table is CurrencyRates);
MainData:
LOAD Product,
Country,
Document_Date,
Currency,
Quantity,
Price,
Price / ApplyMap('Map_Currency_Rate', Currency_Code, 1) AS NewPrice
FROM
MainData.xlsx
(ooxml, embedded labels, table is MainData);
If you face any challenges then please share the application with sample data.
Regards!
Rahul
Hi Rahul!
Thanks for the response! I understand the idea behind the mapping load, but I dont get how the expression picks up the latest currency rate. My data looks like this:
ExchangeRates:
CurrencyCode | CurrencyDate | CurrencyRate
NOK | 01-10-16 | 8.9959
NOK | 01-11-16 | 9.098
NOK | 01-12-16 | 9.0663
EUR | 01-10-16 | 1
EUR | 01-11-16 | 1
EUR | 01-12-16 | 1
USD | 01-10-16 | 1.0925
USD | 01-11-16 | 1.0656
USD | 01-12-16 | 1.0523
MainData:
Product | Currency | Price
ProductABC | NOK | 1500
ProductXYZ | USD | 500
ProductXXX | EUR | 250
Let's take ProductABC as an example. I'd like the expression to divide 1500 by the latest rate available in the data - 9.0663 and put the result in a new column, e.g. PriceEUR. I hope this is possible.
Thanks!
Hello Steve,
Please refer below given draft script:
ExchangeRates:
LOAD * INLINE [
CurrencyCode | CurrencyDate | CurrencyRate
NOK | 01-10-16 | 8.9959
NOK | 01-11-16 | 9.098
NOK | 01-12-16 | 9.0663
EUR | 01-10-16 | 1
EUR | 01-11-16 | 1
EUR | 01-12-16 | 1
USD | 01-10-16 | 1.0925
USD | 01-11-16 | 1.0656
USD | 01-12-16 | 1.0523
](ansi, txt, delimiter is '|', embedded labels);
NoConcatenate
LatestExchangeRates:
LOAD
CurrencyCode AS Currency,
CurrencyDate AS LatestCurrencyDate,
CurrencyRate AS LatestCurrencyRate
Resident ExchangeRates;
INNER JOIN
//Find Latest currency record per currency code & join it with exiting currency data to get the respective currency rate
LatestExchangeRates:
LOAD CurrencyCode AS Currency,
MAX(CurrencyDate) AS LatestCurrencyDate
Resident ExchangeRates
Group By CurrencyCode;
////Create a mapping table
Map_ExchangeRates:
Mapping LOAD
Currency,
LatestCurrencyRate
Resident LatestExchangeRates;
DROP Table LatestExchangeRates;
MainData:
LOAD
Product,
Currency,
Price,
Price/ApplyMap('Map_ExchangeRates', Currency, 1) AS NewPrice;
LOAD * INLINE [
Product | Currency | Price
ProductABC | NOK | 1500
ProductXYZ | USD | 500
ProductXXX | EUR | 250
](ansi, txt, delimiter is '|', embedded labels) ;
Hope this will be of help.
Regards!
Rahul