Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikel_de
Creator
Creator

How to use currency exchange rates in Qlik Sense

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

1 Solution

Accepted Solutions
dineshsingh
Partner - Contributor III
Partner - Contributor III

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

View solution in original post

12 Replies
dineshsingh
Partner - Contributor III
Partner - Contributor III

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

rahulpawarb
Specialist III
Specialist III

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

mikel_de
Creator
Creator
Author

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

rahulpawarb
Specialist III
Specialist III

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

mikel_de
Creator
Creator
Author

Thank you very much! Great explanation!

steve_br
Creator
Creator

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!

rahulpawarb
Specialist III
Specialist III

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

steve_br
Creator
Creator

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!

rahulpawarb
Specialist III
Specialist III

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