Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I’m new in Qlikview. I need to implement currency conversion in order to convert the values available in a table for any of the currencies proposed (Norwegian Krone (NOK), Euro, USD or Pound (GBP), by the date (currencies rates base in monthly values) .
I have a currency rate table, with all the corresponding values for the currencies, where I have a field with the concatenated values: ‘From-To-Year-Month’ that is called exchkey.
I have tried to use the functions FieldValue() and FieldIndex() to get the corresponding Rate value from the table. At the beginning it seems to work ok, but I have realized it don’t return the correct rate value, only works for the first value in the table:
Doc Number | Net Value | Rate | Net value coverted | Value 'exchkey' |
4400033474 | Kr 210000,00 | 0,078439 | € 16472,19 | NOK-EUR-2014-1 |
4400033481 | € 320000,00 | 1 | € 320000,00 | EUR-EUR-2014-1 |
4400033485 | $ 20880,00 | 8,838021 | € 184537,88 | USD-EUR-2014-1 |
4400033502 | Kr 1361840,00 | 0,078439 | € 106821,37 | NOK-EUR-2014-1 |
Exch. key | ExRt | From | To | Curr Date | Exch. Rate | Curr year | Curr month |
EUR-EUR-2014-1 | XRATE | EUR | EUR | 20140101 | 1 | 2014 | 1 |
NOK-EUR-2014-1 | XRATE | NOK | EUR | 20140101 | 0.119213 | 2014 | 1 |
NOK-GBP-2016-1 | XRATE | NOK | GBP | 20160101 | 0.078439 | 2016 | 1 |
USD-EUR-2014-1 | XRATE | USD | EUR | 20140101 | 0.734198 | 2014 | 1 |
USD-NOK-2016-1 | XRATE | USD | NOK | 20160101 | 8.838021 | 2016 | 1 |
Could you help me with this issue?. I would like to know if there are a mistake in the way I’m using FieldValue formula, or if the currency conversion should be done in the script side and not during the expressions calculation?.
I have check another posts but I didn't found any similar case.
Attached is the example of the application I have use.
Thanks in advance.
Your first mistake is attempting to use FieldValue for this purpose. FieldValue gets values from the field's symbol table, and has no knowledge of nor interest in the associative structure of the Qlikview data model.
I would join the currency rates into the fact table based on the date and the currency of the transaction, and the required currencies to convert to. The simplest would be a single output currency, so you would need to add a single rate field with the join. Then your expression would simply multiply (or divide) the transaction amount by the rate value joined to the transaction.
I would perform the joining of the rates to the transactions in the load script. The conversion to a target currency can be done in the load script or in the front end.
Your first mistake is attempting to use FieldValue for this purpose. FieldValue gets values from the field's symbol table, and has no knowledge of nor interest in the associative structure of the Qlikview data model.
I would join the currency rates into the fact table based on the date and the currency of the transaction, and the required currencies to convert to. The simplest would be a single output currency, so you would need to add a single rate field with the join. Then your expression would simply multiply (or divide) the transaction amount by the rate value joined to the transaction.
I would perform the joining of the rates to the transactions in the load script. The conversion to a target currency can be done in the load script or in the front end.
Hi Jonathan,
Thank for your prompt answer.
This solutions means I need to join so many rates as currencies I have: Rate_NOK, Rate_USD, Rate_GBP, Rate EUR and so on, right?.
Do you have any example of the join?.
How many currencies do you need to report in? I would convert everything to a standard currency when loading so you need to join in the conversions to convert to the standard - these can be discarded after the load.
Then you need to load the conversions from the standard to the reporting currency.These will be used to dynamically convert the amounts in the front end to the user-selected reporting currency. I have done this with about 20 transaction currencies and 5 reporting currencies. You need to join a few rates but that should not be too onerous.
Unfortunately, I don't have a suitable example for you at the moment. You might be able to find an example if you search the community site.
Hi Jonathan, Thanks for your support. I will try your proposal.
Here is a simplified example:
// Load FX rates
FXSpot:
LOAD
Date,
FromCcy,
'ZAR' As ToCcy,
Rate
FROM FXSpot.csv;
// Build mapping tables containing reporting exhange rates
// Since the FX Rates are always 'to ZAR', use the inverse to convert from ZAR
T_Rates:
MapUSD:
Mapping LOAD Date As TradeDate,
1/Rate As USD
Resident FXSpot
Where FromCcy = 'USD' And ToCcy = 'ZAR';
MapGBP:
Mapping LOAD Date As TradeDate,
1/Rate As GBP
Resident FXSpot
Where FromCcy = 'GBP' And ToCcy = 'ZAR';
MapEUR:
Mapping LOAD Date As TradeDate,
1/Rate As EUR
Resident FXSpot
Where FromCcy = 'EUR' And ToCcy = 'ZAR';
// Load main fact table
T_Trades:
LOAD
TradeNo,
TradeDate,
Client,
Currency,
Amount.
1 As ZAR,
ApplyMap('MapUSD', TradeDate, 1) As USD, // defaults missing to '1'
ApplyMap('MapGBP', TradeDate, 1) As GBP, // may want to default to '0'
ApplyMap('MapEUR', TradeDate, 1) As EUR,
FROM Trades.csv;
// Bring in all the rates to convert the trade amounts to ZAR
Left Join(T_Trades)
LOAD
Date As TradeDate,
FromCcy As Currency,
Rate
Resident FXSpot
Where ToCcy = 'ZAR';
// Compute the ZAR amount.
// Keep the Base currency amount and/or TradeExchangeRate if required.
Trades:
NoConcatenate
LOAD TradeNo,
TradeDate,
Client,
Currency,
Amount as AmountBase,
Amount * Alt(Rate, 1) As Amount,
Rate As TradeExchangeRate
Resident T_Trades;
// Clean up
DROP Table T_Trades;
DROP Table FXSpot;
/*
Create a variable vCurrency in an input box
Constrain to 'ZAR', 'USD', 'GBP', 'EUR'
In your expressions for amount:
Sum({<......>} Amount * [$(vCurrency)] )
*/
Create a variable vCurrency in an input box
Constrain to 'ZAR', 'USD', 'GBP', 'EUR'
In your expressions for amount:
Sum({<......>} Amount * [$(=vCurrency)])