12 Replies Latest reply: Feb 13, 2017 12:54 AM by dineshraj ramesh RSS

    How to use currency exchange rates in Qlik Sense

    Mikel De

      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

        • Re: How to use currency exchange rates in Qlik Sense
          Dinesh Singh

          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

          • Re: How to use currency exchange rates in Qlik Sense
            Rahul Pawar

            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

            • Re: How to use currency exchange rates in Qlik Sense
              Mikel De

              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

                • Re: How to use currency exchange rates in Qlik Sense
                  Rahul Pawar

                  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

                • Re: How to use currency exchange rates in Qlik Sense
                  Steve Br.

                  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!

                    • Re: How to use currency exchange rates in Qlik Sense
                      Rahul Pawar

                      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

                        • Re: How to use currency exchange rates in Qlik Sense
                          Steve Br.

                          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!

                            • Re: How to use currency exchange rates in Qlik Sense
                              Rahul Pawar

                              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

                        • Re: How to use currency exchange rates in Qlik Sense
                          dineshraj ramesh

                          Hi Mike,

                           

                          Please find the below attachment.

                           

                          Thanks

                          Dineshraj