Date range match with key

[pauldamen]

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

[Anonymous]

Hi Paul,

Could you attach one reduced example of this plz?

Thanks!

[pauldamen]

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

[sunny_talwar · Accepted solution]

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;

[Anonymous]

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,

    To

RESIDENT Range;

Left Join (Main)

LOAD

    Key,

    Date,

    [Time Budget]

RESIDENT Range;

DROP TABLE Range;

Regards!!

[pauldamen]

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

[sunny_talwar]

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

or here: Qlikview Tutorial: Extended IntervalMatch