topic Re: Match two time intervals in QlikView

Match two time intervals

Thu, 10 Mar 2016 23:15:15 GMT

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

Re: Match two time intervals

swuehl — Thu, 10 Mar 2016 23:24:39 GMT

Which example?

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

Re: Match two time intervals

MarcoWedel — Thu, 10 Mar 2016 23:25:37 GMT

Hi,

maybe this helps

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

(pages 17ff.)

regards

Marco

Re: Match two time intervals

Digvijay_Singh — Fri, 11 Mar 2016 06:49:11 GMT

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;

Re: Match two time intervals

Fri, 11 Mar 2016 07:32:38 GMT

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

Re: Match two time intervals

Fri, 11 Mar 2016 07:34:06 GMT

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.

Re: Match two time intervals

Gysbert_Wassenaar — Fri, 11 Mar 2016 08:28:44 GMT

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;

Re: Match two time intervals

Fri, 11 Mar 2016 08:43:01 GMT

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

];

Re: Match two time intervals

Fri, 11 Mar 2016 08:45:03 GMT

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

Re: Match two time intervals

Gysbert_Wassenaar — Fri, 11 Mar 2016 09:15:51 GMT

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;

Re: Match two time intervals

Digvijay_Singh — Fri, 11 Mar 2016 09:27:48 GMT

As per logic start date and end date are picked up from different source Ids, It seems you need two Ids in one final record, one for start date source record and another for end date source record. Not sure though.

Re: Match two time intervals

swuehl — Fri, 11 Mar 2016 10:17:16 GMT

I am bit late today.

There was a recent similar discussion on how to match intervals with IDs, have a look at

How to check whether certain record timestamps overlap...

It should demonstrate also how to interval match the subintervals back into the original intervals (so you get the information for each subinterval which original intervals contribute).

In the referenced sample QVW, there are two keys to consider, localilty and machine ID, so I think you should be able easily create a solution for your specific requirements.

Best,

Stefan

Re: Match two time intervals

Fri, 11 Mar 2016 12:30:38 GMT

Indeed, it'd look strange with 2015-02-31 🙂 Very many thanks!