topic How to use FieldValue() for Currency Convertion in QlikView

How to use FieldValue() for Currency Convertion

Tue, 16 Feb 2016 09:11:17 GMT

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.

Re: How to use FieldValue() for Currency Convertion

jonathandienst — Tue, 16 Feb 2016 10:57:50 GMT

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.

Re: How to use FieldValue() for Currency Convertion

Tue, 16 Feb 2016 11:50:58 GMT

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?.

Re: How to use FieldValue() for Currency Convertion

jonathandienst — Wed, 17 Feb 2016 09:45:37 GMT

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.

Re: How to use FieldValue() for Currency Convertion

Wed, 17 Feb 2016 10:02:06 GMT

Hi Jonathan, Thanks for your support. I will try your proposal.

Re: How to use FieldValue() for Currency Convertion

jonathandienst — Wed, 17 Feb 2016 13:32:03 GMT

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)])