Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Intervalmatch with overlapping date ranges

Perhaps i am approaching this wrong, but my class documentation (7.0) implied this works with overlapping data and all the samples appears to fail in this case. My sample code is;

periods_tmp:
LOAD * INLINE [
Period, DStart, DEnd
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];

periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;

drop table periods_tmp;

history:
Load *;
SQL select * from History where createdate > getdate()-100;


LEFT JOIN (history)
Intervalmatch (CREATEDATE)
Load PStart, PEnd Resident [periods];

This fails to get me wahat I want and i don't think its a syntax issue. Any suggestions would help here. i can load the history table with a lot of booleans tied to the periods, make a intermidiate table of periods to history dates and repetitively load it or is there another method I'm missing with the Inertvalmatch

Thanks







periods_tmp:



LOAD * INLINE [
Period, DStart, DEnd
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;

drop table periods_tmp;

history:
Load *;
SQL select * from History where createdate > getdate()-100;


LEFT JOIN (history)
Intervalmatch (CREATEDATE)
Select PStart, PEnd Resident periods;





1 Solution

Accepted Solutions
Not applicable
Author

I finally got the right combination of syntax and it worked,

This worked,

periods_tmp:
LOAD * INLINE [
Period, DEnd, DStart
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];

//Convert strings to Dates
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;

//Drop Temp Table
drop table periods_tmp;

//Load History and Strip TimeStamp

history:
Load Floor(Createdate) as Createdate, HistoryID;
SQL select Createdate, HistoryID
from History
;

// This Generates a syn$ Table on Start/End - but does the binding.
LEFT JOIN (history)
Intervalmatch (Createdate)
Load PStart, PEnd Resident periods;

// NEED TO DROP SYN$ TABLE !!! - So ...
// Add Period to Table based on PStart/PEnd
LEFT JOIN (history)
LOAD
PStart
,PEnd
,Period
RESIDENT periods;

// Dropping Perios Drops the Syn$ Table too
DROP TABLE periods;

View solution in original post

1 Reply
Not applicable
Author

I finally got the right combination of syntax and it worked,

This worked,

periods_tmp:
LOAD * INLINE [
Period, DEnd, DStart
2009-08, 9/1/2009, 5/1/2009
2009-07, 8/1/2009, 4/1/2009
2009-06, 7/1/2009, 3/1/2009
2009-05, 6/1/2009, 2/1/2009
2009-04, 5/1/2009, 1/1/2009
2009-03, 4/1/2009, 12/1/2008
2009-02, 3/1/2009, 11/1/2008
];

//Convert strings to Dates
periods:
Load Period, Date#(DStart) as PStart, Date#(DEnd) as PEnd
Resident periods_tmp;

//Drop Temp Table
drop table periods_tmp;

//Load History and Strip TimeStamp

history:
Load Floor(Createdate) as Createdate, HistoryID;
SQL select Createdate, HistoryID
from History
;

// This Generates a syn$ Table on Start/End - but does the binding.
LEFT JOIN (history)
Intervalmatch (Createdate)
Load PStart, PEnd Resident periods;

// NEED TO DROP SYN$ TABLE !!! - So ...
// Add Period to Table based on PStart/PEnd
LEFT JOIN (history)
LOAD
PStart
,PEnd
,Period
RESIDENT periods;

// Dropping Perios Drops the Syn$ Table too
DROP TABLE periods;