4 Replies Latest reply: May 12, 2010 4:06 AM by marianasoares RSS

    Converting Currencies using a database currency table

       

      Hello,

      I'm trying to implement a procedure for converting currencies. I've seen that it's possible to invoke an available web service and get the results in runtime, this is presented in the demo 'What's New in QV9' . Even though this approach has some benefits, for example always having the latest currency information, it has some disadvantages too, need to subscribe and pay this service and hoping that the webservice is always up and available. As a result, I'd like to ask you if it's possible to have this information in a database table and access it in the report? Meaning, after running the script and having the report available, is it possible to have a listbox with the available currencies and after choosing one specific currency, all the values presented in the graphics and tables would be automatically converted for the chosen currency?

      Thanks in advance for your time,

      Mariana Soares



        • Converting Currencies using a database currency table
          juergm

          Hi Mariana

          You will maintain currency exchange rates then manually (inline or in an excel table)?

          I would decide on a "standard" currency for all values. Create a table with fields currency and exchange_rate. Exchange rates will be the factor for calculating the currency from your standard currency. In the load statements convert all amounts to the standard currency if needed.

          Create a listbox for the currencies, select a single one and then set the general propertiy to "Always one selected value".

          In your tables/charts multiply the values with the selected Exchange rate (and show the currency in addition to the listbox if you want).

          Regards

          Jürg

            • Converting Currencies using a database currency table

              Hello Jürg,

               

              Thank you very much for your reply. I'll try your solution and I'll give you feedback afterwards.

              Thanks once again.

              Best regards,
              Mariana


                • Converting Currencies using a database currency table
                  John Witherspoon

                  Let's say you have a sales database, where you're selling in many different countries. You also have an exchange rate database, say with an average exchange rate for each day, or however you want to handle it. So your raw data looks like this:

                  RawSales:
                  LOAD * INLINE [
                  Sale, Date, Item, Currency, Amount
                  1, 2008-05-10, A, RUB, 3000
                  2, 2008-05-20, A, USD, 120
                  3, 2008-06-15, B, EUR, 100
                  ];
                  ExchangeRatesRUB: // Our standard currency will be Russian Rubles, but you could pick anything
                  LOAD * INLINE [
                  Date, Currency, Rate
                  2008-05-10, RUB, 1
                  2008-05-10, EUR, 15
                  2008-05-10, USD, 10
                  2008-05-20, RUB, 1
                  2008-05-20, EUR, 16
                  2008-05-20, USD, 12
                  2008-06-15, RUB, 1
                  2008-06-15, EUR, 20
                  2008-06-15, USD, 10
                  ];

                  In order to simply select a currency and have QlikView do the rest for you without any complicated formulas, I believe you want to transform these tables into this:

                  Sale Date Item

                  1 2008-05-10 A
                  2 2008-05-20 A
                  3 2008-06-15 B

                  Sale Currency Amount

                  1 3000 RUB
                  1 45000 EUR
                  1 30000 USD
                  2 10 RUB
                  2 160 EUR
                  2 120 USD
                  3 5 RUB
                  3 100 EUR
                  3 50 USD

                  You can do that like this:

                  Sales:
                  LOAD
                  Sale
                  ,Date
                  ,Item
                  RESIDENT RawSales
                  ;
                  SalesAmounts:
                  LOAD
                  Sale
                  ,Date as ExchangeRateDate
                  ,Amount as OriginalAmount
                  ,Currency as OriginalCurrency
                  RESIDENT RawSales
                  ;
                  // Join exchange rate for the original currency on that date
                  LEFT JOIN (SalesAmounts)
                  LOAD
                  Date as ExchangeRateDate
                  ,Currency as OriginalCurrency
                  ,Rate as ToRublesRate
                  RESIDENT ExchangeRatesRUB
                  ;
                  // Divide to convert to Rubles
                  LEFT JOIN (SalesAmounts)
                  LOAD
                  Sale
                  ,OriginalAmount/ToRublesRate as Rubles
                  RESIDENT SalesAmounts
                  ;
                  // Join exchange rates for ALL currencies on that date. Creates one row for each Sale for each Currency.
                  LEFT JOIN (SalesAmounts)
                  LOAD
                  Date as ExchangeRateDate
                  ,Currency
                  ,Rate
                  RESIDENT ExchangeRatesRUB
                  ;

                  LEFT JOIN (SalesAmounts)
                  LOAD
                  Sale
                  ,Currency
                  ,Rubles*Rate as Amount
                  RESIDENT SalesAmounts
                  ;
                  DROP TABLES
                  RawSales
                  ,ExchangeRatesRUB
                  ;
                  DROP FIELDS
                  OriginalAmount
                  ,OriginalCurrency
                  ,ToRublesRate
                  ,Rubles
                  ,Rate
                  ,ExchangeRateDate
                  ;

                  Stick a list box for Currency on the screen, set it to always one selected value, and I believe everything will calculate correctly from there.