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