Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use FieldValue() for Currency Convertion

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 NumberNet ValueRate  Net value covertedValue 'exchkey'
4400033474Kr 210000,000,078439€ 16472,19NOK-EUR-2014-1
4400033481€ 320000,001€ 320000,00EUR-EUR-2014-1
4400033485$ 20880,008,838021€ 184537,88USD-EUR-2014-1
4400033502Kr 1361840,000,078439€ 106821,37NOK-EUR-2014-1

      

Exch. keyExRtFromToCurr Date Exch. RateCurr yearCurr month
EUR-EUR-2014-1XRATEEUREUR201401011   20141
NOK-EUR-2014-1XRATENOKEUR201401010.119213   20141
NOK-GBP-2016-1XRATENOKGBP201601010.078439   20161
USD-EUR-2014-1XRATEUSDEUR201401010.734198   20141
USD-NOK-2016-1XRATEUSDNOK201601018.838021   20161

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.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein