Match to closest FX RATE (Date)

Anonymous — Wed, 28 Oct 2015 09:04:27 GMT

Good day,

I have a PRICE table with entries for a certain currency and I have an FX_RATE table with a conversion factor.

I would like to join these two tables, however I need to join to the closest matching date.

I only have one date value in each table and therefore I can not use a IntervalMatch.

I also can not use a normal join since there aren't records for each date.

The source table look as follows: (Please note these are just sample values)

PRICES:

LOAD * INLINE [PRICE DATE, PORTFOLIO, PRICE_CURRENCY, PRICE_VAL

2015/10/01, 'PORTFOLIO 1', 'EUR', 100,

2015/10/03, 'PORTFOLIO 1', 'EUR', 150,

2015/10/03, 'PORTFOLIO 1', 'USD', 10,

2015/10/15, 'PORTFOLIO 1', 'EUR', 180];

FX_RATE:

LOAD * INLINE [FX_DATE, CURRENCY, RATE,

2014/11/01, 'EUR', 0.01,

2014/11/02, 'EUR', 0.012,

2015/10/02, 'EUR', 0.013,

2015/10/03, 'EUR', 0.014,

2015/10/10, 'EUR', 0.015,

2014/11/01, 'USD', 1,

2015/10/02, 'USD', 2,

2015/10/14, 'USD', 3];

I would like to end up with a target table which looks as follows:

NEW_PRICES_TABLE:

PRICE DATE, PORTFOLIO, PRICE_CURRENCY, PRICE_VAL, FX_RATE ("joined" value to PRICES)

2015/10/01, 'PORTFOLIO 1', 'EUR', 100, 0.012 (fx date = 2014/11/02 and CURRENCY = EUR)

2015/10/03, 'PORTFOLIO 1', 'EUR', 150, 0.014 (fx_date = 2015/10/03 and CURRENCY = EUR)

2015/10/03, 'PORTFOLIO 1', 'USD', 10, 2 (fx_date = 2015/10/02 and CURRENCY = USD)

2015/10/15, 'PORTFOLIO 1', 'EUR', 180, 0.015 (fx_date = 2015/10/10 and CURRENCY = EUR)

I would like to do this in the script part of the QlikView application.

Your assistance is highly appreciated.

Kind regards

Edwin

topic Match to closest FX RATE (Date) in QlikView