Please have a look at the below query. Hope this solves your problem.
Upper(Currency_Name)&Date(Currency_Month,'YYYYMM') as Key,
(ooxml, embedded labels, table is Rates);
Price/ApplyMap('Map_CurrMonth_ExchRate',Upper(Currency)&Date(Document_Date,'YYYYMM')) as Price_EUR
(ooxml, embedded labels, table is Sample);
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.
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!
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.
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?
If CurrencyRates table contains unique currency codes then please use below given sample script.
(ooxml, embedded labels, table is CurrencyRates);
Price / ApplyMap('Map_Currency_Rate', Currency_Code, 1) AS NewPrice
(ooxml, embedded labels, table is MainData);
If you face any challenges then please share the application with sample data.
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:
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
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.
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.
CurrencyConversion_New.qvw 159.8 K