topic Converting Currencies using a database currency table in QlikView

Converting Currencies using a database currency table

Tue, 11 May 2010 13:19:55 GMT

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

Tue, 11 May 2010 13:59:02 GMT

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

Tue, 11 May 2010 14:20:43 GMT

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

johnw — Tue, 11 May 2010 22:16:23 GMT

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.

Converting Currencies using a database currency table

Wed, 12 May 2010 08:06:22 GMT

Thank you very much for both.

I could handle this with your help!