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!
@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?
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.
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.