Match two time intervals

[#1]

Hi,

I need to match two time intervals like in the below example. I found this example but looks too complicated. Any idea how to get it easier? I've tried with master calendar but didn't get me to the needed results.

Intervals1:

StartDate1	EndDate1
2015-05-01	2015-05-31

Intervals2:

StartDate2	EndDate2
2015-04-15	2015-05-15

Results:

StartDate1	EndDate1
2015-04-15	2015-04-30
2015-05-01	2015-05-15
2015-05-16	2015-05-31

Regards,

Przemek

[swuehl]

Which example?

And could you comment on how you retrieve the resulting records from your input records?

[MarcoWedel]

Hi,

maybe this helps

https://community.qlik.com/servlet/JiveServlet/download/4310-5-63650/IntervalMatch%20and%20Slowly%20...

(pages 17ff.)

regards

Marco

[Digvijay_Singh]

See if this sample can help, your logic of adding or subtracting 1 sometimes in start date or end date is complicated.

I tried to first combined all the dates in one field and then created interval through script.

T1:

Load Date(Date#(StartDate1,'YYYY-MM-DD'),'YYYY-MM-DD') as Date

inline [

StartDate1, EndDate1

2015-05-01,2015-05-31 ];

Load Date(Date#(EndDate1,'YYYY-MM-DD'),'YYYY-MM-DD') as Date

inline [

StartDate1, EndDate1

2015-05-01,2015-05-31 ];

Load Date(Date#(StartDate1,'YYYY-MM-DD'),'YYYY-MM-DD') as Date

inline [

StartDate1, EndDate1

2015-05-01,2015-05-31 ];

Load Date(Date#(StartDate2,'YYYY-MM-DD'),'YYYY-MM-DD') as Date

inline [

StartDate2, EndDate2

2015-04-15,2015-05-15 ];

Load Date(Date#(EndDate2,'YYYY-MM-DD'),'YYYY-MM-DD') as Date

inline [

StartDate2, EndDate2

2015-04-15,2015-05-15 ];

Let TotalRows = NoOfRows('T1');

For i = 1 to $(TotalRows)-2

  T2:

  Load $(i) as SNO,

  if($(i)=1,min(Date(Date#(Date,'YYYY-MM-DD'),'YYYY-MM-DD'),$(i)),

    min(Date(Date#(Date,'YYYY-MM-DD')+1,'YYYY-MM-DD'),$(i))) as StartDate,

  min(Date(Date#(Date,'YYYY-MM-DD'),'YYYY-MM-DD'),$(i)+1) as EndDate

  Resident T1

  Order By Date;

NEXT;

[#5]

Hi Marco,

so, yeas - my case is Henric's type "Two interval tables mapped against a common dimension ID and a common time line". The actual datamodel includes also id to be matched. Could you please give me some more hint on how to create the bridge tables and subintervals?

Intervals1:

Id	StartDate1	EndDate1
1	2015-05-01	2015-05-31

Intervals2:

Id	StartDate2	EndDate2
1	2015-04-15	2015-05-15

Results:

Id	StartDate1	EndDate1
1	2015-04-15	2015-04-30
1	2015-05-01	2015-05-15
1	2015-05-16	2015-05-31

Regards,

Przemek

[#6]

Forgot to paste link - this was Henric's example: https://community.qlik.com/thread/75435?q=Match%20two%20intervals

I need to retrieve the records by matching the intervals.

[Gysbert_Wassenaar]

This should do the trick:

Intervals:
LOAD * INLINE [
StartDate, EndDate
2015-05-01, 2015-05-31
2015-04-15, 2015-05-15
];

TempDates:
LOAD StartDate as Date, 'Start' as Type Resident Intervals;
LOAD EndDate as Date, 'End' as Type Resident Intervals;

NewIntervals:
LOAD * WHERE End>0;
LOAD
  if(Type = 'Start', Date, Date(Date+1)) as Start,
  if(Type = 'Start' and Previous(Type) = 'Start', Date(Previous(Date)-1),Date(Previous(Date))) as End
Resident TempDates Order By Date desc;

Drop Table TempDates;

[#8]

Thank you, Gysbert. Looks tempting because of its easiness. But how would it look like if we had intervals as such:

Intervals: 

LOAD * INLINE [ 

Id, StartDate, EndDate 

1, 2015-05-01, 2015-05-31 

1, 2015-04-15, 2015-05-15

2, 2015-02-01, 2015-02-31 

2, 2015-01-15, 2015-02-15

];

[#9]

Thank you, Digvijay. I've tried to integrate Id column into your code but failed. Would you be able to help with it?

[Gysbert_Wassenaar · Accepted solution]

It would look quite strange since 2015-02-31 is not a valid date.

If you want to add Id's to the mix it will look like this:

Intervals: 
LOAD 
  Id, 
  Date#(StartDate,'YYYY-MM-DD') as StartDate, 
  Date#(EndDate,'YYYY-MM-DD') as EndDate  
INLINE [ 
Id, StartDate, EndDate 
1, 2015-05-01, 2015-05-31 
1, 2015-04-15, 2015-05-15
2, 2015-02-01, 2015-02-28 
2, 2015-01-15, 2015-02-15
];

TempDates:
LOAD Id, StartDate as Date, 'Start' as Type Resident Intervals;
LOAD Id, EndDate as Date, 'End' as Type Resident Intervals;

NewIntervals:
LOAD * WHERE End>0;
LOAD 
  Id,
  if(Type = 'Start', Date, If(Id = Previous(Id), Date(Date+1))) as Start,
  if(Id = Previous(Id), 
  if(Type = 'Start' and Previous(Type) = 'Start', 
  Date(Previous(Date)-1), 
  Date(Previous(Date)
  )
  )
  ) as End
Resident TempDates Order By Id, Date desc;

Drop Table TempDates;