topic Date range match with key in QlikView

Date range match with key

pauldamen — Tue, 14 Jun 2016 10:11:26 GMT

Hi,

I have a table with 4 fields; a key, From Date, To Date, Budget time

This should be matched to all dates within the range in another table. So per key the budget time should be match to all dates between the from and to.

I tried an intervalmatch and with the InterNo() but can't get it to work. I only found examples with the from and to date but how to match this per key and assign the budget time to all dates in between?

Regards, Paul

Re: Date range match with key

Anonymous — Tue, 14 Jun 2016 10:16:22 GMT

Hi Paul,

Could you attach one reduced example of this plz?

Thanks!

Re: Date range match with key

pauldamen — Tue, 14 Jun 2016 10:22:14 GMT

Hi,

Sorry I can't my NDA prevents this

But I can write it down how the tables look

Table to be matched:

Key From To Time Budget

123 1/1/15 1/5/15 28

123 1/5/15 14/6/16 14

Main Table:

Key Date TransactionAmount Qty etc etc

123 1/3/15 15 14

123 15/5/15 30 18

So to first line in the second table the Time budget Column should show 28. The second row should show 14. This should be done for all keys over all dates.

Regards, Paul

Re: Date range match with key

sunny_talwar — Tue, 14 Jun 2016 10:28:59 GMT

Try this may be:

Table1:

LOAD * INLINE [

Key, From, To, Time Budget

123, 1/1/15, 1/5/15, 28

123, 1/5/15, 14/6/16, 14

];

Table2:

LOAD * INLINE [

Key, Date, TransactionAmount, Qty

123, 1/3/15, 15, 14

123, 15/5/15, 30, 18

];

Left Join (Table1)

IntervalMatch(Date, Key)

LOAD From,

To,

Key

Resident Table1;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

Re: Date range match with key

Anonymous — Tue, 14 Jun 2016 10:30:54 GMT

Hi Paul,

Try this code:

Range:

LOAD * Inline [

Key, From, To, Time Budget

123, 01/01/2015, 01/05/2015, 28

123, 01/05/2015, 14/06/2016, 14

];

Main:

LOAD * Inline [

Key, Date, TransactionAmount

123, 01/03/2015, 15

123, 15/05/2015, 30

];

Left Join (Range)

IntervalMatch (Date)

LOAD

From,

RESIDENT Range;

Left Join (Main)

LOAD

Key,

Date,

[Time Budget]

RESIDENT Range;

DROP TABLE Range;

Regards!!

Re: Date range match with key

pauldamen — Tue, 14 Jun 2016 11:09:47 GMT

Thanks! That works! Didn't know you could enter more arguments in the intervalmatch

Re: Date range match with key

sunny_talwar — Tue, 14 Jun 2016 11:14:31 GMT

This is called Extended IntervalMatch. Read about it in the help section: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/IntervalMatch_(Extended_Syntax).htm

or here: Qlikview Tutorial: Extended IntervalMatch