5 Replies Latest reply: Apr 30, 2015 3:05 AM by Johannes Mathis RSS

    Currency translation

    Johannes Mathis

      Hi Community,

       

      I'm a very beginner in QV and I have a question regarding currency translation.

       

      I have two tables which I hvae connected via "Local Currency" and "Currency".

       

      Table1

      Invoice Number

      Amount

      Document Currency

      Document CurrencyLocal Currency
      1200USDCHF
      2150EURCHF
      3300CHFCHF
      435000THBCHF
      5700USDEUR

       

      Table2

      Currencyfx-rate BU vs EUR
      CHF1,3
      USD0,9
      THB40
      EUR1

       

      Now in the script I want QV to create an new dimension "Amount DC @ LC" by calculating the "Amount Document Currency" translated in Local Currency @ BU fx-rate.

       

      I would like to have the following result:

       

      Invoice NumberNEW: Amount DC @ LCLocal Currency
      1288,89 (=200/0,9*1,3)CHF
      2195 (=150/1*1,3)CHF
      3 300 (=300/1,3*1,3)CHF
      41137,5 (=35000/40*1,3)CHF
      5 777,78 (=700/0,9*1)EUR

       

      How is it possible?

       

      Thanks and kind regards

      Johannes

        • Re: Currency translation
          Jordi Arenas

          HI,

           

          Currencys:

          Mapping LOAD Currency as [Local Currency],

               [fx-rate BU vs EUR] as EUR

          FROM

          [C:\Users\jarenas\Desktop\Test\Libro1.xlsx]

          (ooxml, embedded labels, table is Hoja3, filters(

          Remove(Row, Pos(Top, 6))

          ));

           

           

          Final_table:

          LOAD *,

          EUR * Amount AS New_Value;

          LOAD *,

          ApplyMap('Currencys',[Local Currency],'N/D') As EUR

          FROM

          [C:\Users\jarenas\Desktop\Test\Libro1.xlsx]

          (ooxml, embedded labels, table is Hoja2);

           

          Regards

          • Re: Currency translation
            Gysbert Wassenaar

            You can use a mapping table and the applymap function:

             

            MapCurrency:
            MAPPING LOAD
                 Currency,
                 [fx-rate BU vs EUR ]
            FROM ...
            
            Invoices:
            LOAD
                 [Invoice Number],
                 [Amount Document Currency],
                 [Document Currency],
                 [Local Currency],
                 [Amount Document Currency]
                      / applymap('MapCurrency',[Document Currency],1)
                      * applymap('MapCurrency',[LocalCurrency],1) as [NEW: Amount DC @ LC]
            FROM ...
            
            
            • Re: Currency translation
              Massimo Grossi

              1.png

              e:

              LOAD Currency,     [fx-rate BU vs EUR]

              FROM

              [https://community.qlik.com/thread/162199]

              (html, codepage is 1252, embedded labels, table is @2);

               

              f:

              LOAD [Invoice Number],     [AmountDocument Currency],     [Document Currency],     [Local Currency]

              FROM

              [https://community.qlik.com/thread/162199]

              (html, codepage is 1252, embedded labels, table is @1);

               

              left join (f)

              load Currency as [Document Currency],     [fx-rate BU vs EUR] as [Document Currency X]

              Resident e;

               

              left join (f)

              load Currency as [Local Currency],     [fx-rate BU vs EUR] as [Local Currency X]

              Resident e;

               

              left join (f

              load *,  [AmountDocument Currency] / [Document Currency X] * [Local Currency X] as NewAmount

              Resident f;

              • Re: Currency translation
                Alex Millan

                Hi Johannes,

                 

                See the example attached.

                 

                HTH

                 

                Regards,