Intervalmatch with overlapping date ranges

Fri, 25 Sep 2009 04:27:14 GMT

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;

Intervalmatch with overlapping date ranges

Fri, 25 Sep 2009 20:42:58 GMT

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;

topic Intervalmatch with overlapping date ranges in QlikView

Intervalmatch with overlapping date ranges

Intervalmatch with overlapping date ranges