topic Re: checking times with in a range of different time periods - need help plz.. in QlikView

checking times with in a range of different time periods - need help plz..

lavanya14 — Thu, 30 Oct 2014 10:31:30 GMT

Hi All,

I would like to know if there is any way to check if the two different times fall with in a range of time period. Let me explain you with an example..

Lets say i have different slots

Name working - starttime working- endtime

ABC 30/10/2014 06:00:00 30/10/2014 08:00:00

ABC 30/10/2014 10:00:00 30/10/2014 12:00:00

XYZ 30/10/2014 08:00:00 30/10/2014 10:00:00

Lets say a delay has happened for 15mins and the start time of delay is 30/10/2014 07:50:00 and the end time would be

30/10/2014 08:05:00. this delay falls in the range of both ABC and XYZ working time slots.

I wanted to know if there is anyway i can pass the start time and endtime of delays to the working-starttime and working - endtime at the same time so that i can find if the delay is falling in two different persons working times or not.

Any suggestions are appreciated..

Thanks in advance.

Using interval match i cna find out if

Re: checking times with in a range of different time periods - need help plz..

Gysbert_Wassenaar — Thu, 30 Oct 2014 11:11:03 GMT

See attached qvw.

Re: checking times with in a range of different time periods - need help plz..

lavanya14 — Thu, 30 Oct 2014 12:10:00 GMT

Thanks alot Gysbert !!

Re: checking times with in a range of different time periods - need help plz..

lavanya14 — Thu, 30 Oct 2014 14:21:59 GMT

Hi Gysbert,

Just a small clarification.. This solution works good when i have less no of data but my data is huge and the application is hanging.. any suggestions plz?

Re: Re: checking times with in a range of different time periods - need help plz..

Gysbert_Wassenaar — Thu, 30 Oct 2014 15:04:51 GMT

Yes, I kind of expected that. The solution creates a cartesian product first and then removes most records again by comparing the date fields and keeping only those where the intervals overlap. Perhaps you can process your source data in batches, for example per month or per day.

Another approach would involve using the intervalmatch function. See attached qvw. But the intervalmatch function and the joins may also have performance problems.

Re: checking times with in a range of different time periods - need help plz..

lavanya14 — Thu, 30 Oct 2014 15:49:20 GMT

Thank you !! I have a questions lets say if there is a work done at 30/10/2014 12:00:00.

Name shift start shift end

abc 30/10/2014 10:00:00 30/10/2014 12:00:00

xyz 30/10/2014 12:00:00 30/10/2014 14:00:00

the move falls under both the drivers.. I think the interval match checks >= shift start and <= shift end.. Plz correct me if i am wrong.. i have handled this by making the shift end as 30/10/2014 11:59:59 ... any more suggestions?

thanks