

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date range match with key
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Paul,
Could you attach one reduced example of this plz?
Thanks!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks! That works! Didn't know you could enter more arguments in the intervalmatch

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)....
