Interval match with inner join

Tue, 14 Apr 2015 14:46:04 GMT

Hello There,

I run the below script, got from QV community. I found Inner join behaves unusal here. When i Use "Interval Match with Inner join, is also going to fetch the uncommon rows between both the tables. See the Last row from Data, It will not fall under any one the interval in contract table, Still the row will be loaded when i execute the script.

can any one tell the reason why it behaves like?

Data:

LOAD * INLINE [Date, Key1, Other

     2010-01-01, A, z

     2010-02-01, A, a

     2010-03-01, A, z

     2010-04-01, A, b

     2010-05-01, A, d

     2010-06-01, A, e

     2010-01-01, B, f

     2010-02-01, B, t

     2010-03-01, B, k

     2010-04-01, B, e

     2010-05-01, B, z

     2010-06-01, B, a

     2013-09-01, C, z];

// ===== Load the Contract ================================================

Contract:

LOAD * INLINE [Cntrct_From, Cntrct_To, Key1, Val

     2010-01-01, 2010-12-31, A, 5

     2010-01-01, 2010-03-30, B, 3

     2010-04-01, 2010-12-31, B, 4];

// ==== Link Fields from Data with Contract into the Contract-Table ========

INNER JOIN INTERVALMATCH (Date, Key1) LOAD Cntrct_From, Cntrct_To, Key1 RESIDENT Contract;

Re: Interval match with inner join

Mon, 25 May 2015 07:53:13 GMT

Data:

LOAD * INLINE [Date, Key1, Other

2010-01-01, A, z

2010-02-01, A, a

2010-03-01, A, z

2010-04-01, A, b

2010-05-01, A, d

2010-06-01, A, e

2010-01-01, B, f

2010-02-01, B, t

2010-03-01, B, k

2010-04-01, B, e

2010-05-01, B, z

2010-06-01, B, a

2013-09-01, C, z];

// ===== Load the Contract ================================================

Contract:

LOAD * INLINE [Cntrct_From, Cntrct_To, Key1, Val

2010-01-01, 2010-12-31, A, 5

2010-01-01, 2010-03-30, B, 3

2010-04-01, 2010-12-31, B, 4];

// ==== Link Fields from Data with Contract into the Contract-Table ========

Inner Join

INTERVALMATCH (Date, Key1)

LOAD

Cntrct_From,

Cntrct_To,

Key1&Key1 as key

RESIDENT Contract;

DROP Table Data;

u can create table key column and drop the data table u can get right answer

thanks

topic Interval match with inner join in QlikView

Interval match with inner join

Re: Interval match with inner join