topic Exchange rates with dynamic transaction date in QlikView

Exchange rates with dynamic transaction date

Anonymous — Thu, 19 Nov 2015 11:52:51 GMT

Good day,

I have record entries with amounts based on various currencies.

Then I also have a foreign exchange table, with many exchange rated for each currency for different dates.

I have a chart which takes a variable date as input and I would like to also dynamically derive the exchange rate for that currency on that day. Linking the table on currency_id is not very effective because the dimension brings up unwanted chart lines.

I also tried to link the two tabels with currency but by creating a sub link association.

This too is not very effective since it joins millions of records with millions of exchange rates.

So basically:

ENTRIES:

CURRENCY_ID,

CURR,

ENTRY_ID,

AMOUNT;

FX_RATES:

CURR,

BASE_CURR,

DATE,

RATE;

LET vDate1 = <some date>

The rate chart column is defines as:

max(if(DATE = '$(vDate1)', RATE, null()))

Is there perhaps a more effective way in which one can link the two tables ?

Kind regards

Edwin

Re: Exchange rates with dynamic transaction date

Mark_Little — Thu, 19 Nov 2015 12:12:10 GMT

Hi,

You will need to make some link between date currency id.

If enteries had a date it could be a concatanated key.

CURR&Date on both tables?

Mark

Re: Exchange rates with dynamic transaction date

Anonymous — Thu, 19 Nov 2015 12:50:21 GMT

Hello Mark,

That is the problem, the date is dynamic from a variable.

When the user enters the date the chart calculates all the relevant entries for the aggregation.

I also can not link to a date since the chart works on multiple dates, i.e. maturity dates in the future, etc.

I manager to only load the FX rates for currencies which exists in the entries table, however my second dimension is still showing many record entries which aggregates to 0 but is still showing in the pivot chart.

Kind regards

Edwin