Extended Interval Match

Julie_Main — Thu, 11 Nov 2010 15:51:07 GMT

I am trying to run an extended IntervalMatch and am having some problems (I'm on V9).

I have data in a currency table which has a Currency Code, CurrencyTodate, a CurrencyFromDate and a CurrencyRate. I need to link this to an order table by both date and also by CurrencyCode, to ensure that I have a table that references the correct date range to give me the right rate for the date in which the order falls between.

I ran this for Interval match, but obviously it didn't link the CurrencyCode's from the Order and Currency tables, although it did help with the date range. When I use Extended Interval match, then I get no link at all ..

Anyone got any ideas? I have the following code:

___________________________________

//Currency Rate Table

CurrencyRates:

LOAD CurrencyFromDate,

CurrencyRate,

CurrencyRateDate,

CurrencyToDate,

FromCurrencyCode,

ToCurrencyCode;

SQL SELECT *

FROM CurrencyRates;

INTERVALMATCH (CurrencyDate, [LocalCurrency]) load CurrencyFromDate, CurrencyToDate, ToCurrencyCode

Resident CurrencyRates;

___________________________________

I have also tried the following and that doesn't work either:

___________________________________

CurrencyRates:

LOAD CurrencyFromDate,

CurrencyRate,

CurrencyRateDate,

CurrencyToDate,

FromCurrencyCode,

ToCurrencyCode;

SQL SELECT *

FROM CurrencyRates;

Left Join INTERVALMATCH (CurrencyDate, [LocalCurrency]) load CurrencyFromDate, CurrencyToDate, ToCurrencyCode

Resident CurrencyRates;

Extended Interval Match

pover — Thu, 11 Nov 2010 19:04:28 GMT

The other keyfield has to have the same name in the intervalmatch. In your case the field in both tables should be LocalCurrency. View the example code below:

//Currency Rate Table

CurrencyRates:

LOAD CurrencyFromDate,

CurrencyRate,

CurrencyRateDate,

CurrencyToDate,

FromCurrencyCode,

ToCurrencyCode,

ToCurrencyCode as LocalCurrency ;

SQL SELECT *

FROM CurrencyRates;

INTERVALMATCH (CurrencyDate, [LocalCurrency]) load CurrencyFromDate, CurrencyToDate, LocalCurrency

Resident CurrencyRates;

topic Extended Interval Match in QlikView

Extended Interval Match

Extended Interval Match