topic Re: Working with overlapping date-ranges in QlikView

Working with overlapping date-ranges

joggiek — Thu, 27 Mar 2014 11:02:13 GMT

Hi All,

My company tracks its vehicles with GPS devices that store all kinds of info, but for this query I’m only interested in the trip times.

I have a table that stores the start and end times of all the trips.

With another table that stores the shifts. It only shows the shifts and the start and end times. (No dates)

Weekend-shift 1 starts on Friday 1am to Saturday 1pm

Weekend-shift 2 start on Saturday 1pm to Sunday 1am

I need to be able to show the trips done and also to which Shifts the trips belong. I used IntervalMatch to make this work before, but since then weekend-shifts were added that overlaps with the times of the week-shifts.

I’m stuck and hope somebody can help

The problems I have are:

The trips that run in shifts that span more than 1 day (Shift 3 that runs from 21:00 to 05:00 the next day)

Determining if the trip dates are in the week or weekend

The trips that fall in a weekend-shift where the times overlap with the week-shifts

The trips that fall into more than 1 shift, I should be able to show all the shifts the trip belongs to

I must also be able to select a shift and see the trips that were done in the selected shift(including partial trips)

I also foresee trouble with trips that start in a week-shift and end in a weekend-shift

I must also be able to select a shift and see the trips that happened in that shift(Including partial trips)

See the attachments for data examples

Re: Working with overlapping date-ranges

Colin-Albert — Thu, 27 Mar 2014 11:11:19 GMT

You need to identify the rules on how a trip relates to a shift.

If a shift 3 runs from 21:00 to 05:00, and shift 4 runs from 05:00 to 14:00,

How do you want to identify a trip that runs from 02:00 to 06:00?

Is it associated to Shift 3 because that is when the trip starts?

Is it associated to Shift 4 because that is when the trip ends?

Is it associated with both shifts?

Also should you define Shift3 as running from 12:00 to 04:49:49.999 or Shift 4 as starting at 05:00:00.001 so you do not have a journey that ends at 05:00 linking to both shifts.

Until you have defined these rules, you cannot create the expressions for your charts.

Re: Working with overlapping date-ranges

joggiek — Thu, 27 Mar 2014 11:21:09 GMT

The trips are associated to all shifts it touches

The shift end and the next start on the same time

(Example Shift 1 - 09:00 to 21:00:00 Shift 2 21:00:00 to 05:00:00)

If a trip ends on the exact time (21:00:00) it will show as part of both shifts

Re: Working with overlapping date-ranges

gandalfgray — Mon, 31 Mar 2014 13:53:29 GMT

Hi Joggie

It is not clear how your shifts are defined.

(A trip at 4AM monday morning, does it belong to Shift 3 (of sunday) or to no shift?

A trip at 0:30AM monday morning does it belong to Shift 3 (of sunday) and to WeekEnd 2?)

It may be easier to find a solution if you add a column in your Shift definition file which defines which weekday(s) respective shift starts in.

/gg

Re: Working with overlapping date-ranges

joggiek — Tue, 01 Apr 2014 07:01:05 GMT

Hi,

The trips will belong to both shifts.

I can see the requirement change where they will ask me to split the shifts, but that is a bridge I will cross then.

For now a single trip can belong to multiple shifts.

What I did so far was to split the trips into AM and PM sections, so the trips that spans more than 1 date are now split into a section from, trip start to 00:00:00 and the 2nd section from 00:00:00 to trip end, effectively splitting those trips into 2 separate trips. No I just need to work out how to assign those trips to 1 or more shifts.

Re: Working with overlapping date-ranges

jonathandienst — Tue, 01 Apr 2014 08:15:54 GMT

Joggie

One possible way around this is to work in weeks: create an array of shift times in hours from the start of the week in a temp table, and convert all the trip start and end times to hours from the start of the week. Then do a simple interval match.

So the temp shift table for matching would be:

5, 13, WeekShift1

13, 22, WeekShift2

22, 27, WeekShift3

27, 35, WeekShift1

35, 43, WeekShift2

...

120,125, WeekendShift1

125, 132 WeekendShift2

HTH

Jonathan

Re: Working with overlapping date-ranges

joggiek — Tue, 01 Apr 2014 11:14:48 GMT

Hi Jonathan,

I do not understand the design you mentioned, what are the column headings in that lookup table?

Thanks

Re: Working with overlapping date-ranges

hic — Tue, 01 Apr 2014 11:26:20 GMT

See page 19 in IntervalMatch and Slowly Changing Dimensions

HIC

Re: Working with overlapping date-ranges

joggiek — Tue, 01 Apr 2014 11:53:30 GMT

I just started reading this document, will jump to page 19. Will keep you posted

Thank you

Re: Working with overlapping date-ranges

Tue, 01 Apr 2014 12:13:27 GMT

Hi,

PFA It will give solution for ur Problem.

Regards,

Damu

Re: Working with overlapping date-ranges

hic — Wed, 02 Apr 2014 14:18:04 GMT

Try the attached script.

HIC

Re: Working with overlapping date-ranges

joggiek — Thu, 03 Apr 2014 08:20:07 GMT

Thank you HIC,

I tried the script, but it still seems that the trips that are split over 2 days are not working correctly.

If I select TripId 0A0BD22B-63C0-11E3-8FC3-005056830020 that:

Starts 2014-02-07 20:14:57

Ends 2014-02-08 02:34:57

The results shows that the trip was not part of any shifts, but it should be part of

Shift 2 - 20:14:57 to 20:59:59

Shift 3 - 21:00:00 to 11:59:59

Shift 3 - 12:00:00 to 02:34:57

WeekEnd 2 - 20:14:57 to 11:59:59

WeekEnd 2 - 12:00:00 to 00:59:59

WeekEnd 1 - 01:00:00 to 02:34:57

The same with 0A0A5BBC-5729-11E3-8FC3-005056830020 - 2014-02-05 22:32:01 to 2014-02-06 03:48:01 and a few more?

It looks like it is all the trips that start after 11:59:59 AM?

Maybe I'm still missing something

Thanks for the help

Re: Working with overlapping date-ranges

joggiek — Mon, 07 Apr 2014 07:25:01 GMT

Thank you so much for the help. I got the shifts to work as you explained it HIC.

Re: Working with overlapping date-ranges

joggiek — Tue, 08 Apr 2014 11:30:18 GMT

Hi HIC,

Just a follow-up question.

I have 5 Shifts and 340 000 plus trips, the code is creating an "bridge" for every second from the first to last date.

Is there a way of loosing or trimming the seconds, and only use 'hh:mm'

I tried time(mytime,'hh:mm') , but it does not seem to work

Re: Working with overlapping date-ranges

hic — Tue, 08 Apr 2014 12:27:49 GMT

The Time() function will just format the time, e.g. not show the seconds, but it will not change the underlying value. If you want to truncate every time stamp to nearest minute, you should do

Time(Floor(mytime,1/24/60), 'hh:mm')

However, doing this may cause some rounding problems: The start and end times that contain seconds will lose this information and a trip may then be attributed a shift that ended seconds before the trip started. But if you are OK with this, you should use the above expression.

HIC

Re: Working with overlapping date-ranges

joggiek — Tue, 08 Apr 2014 17:51:18 GMT

Thank you

I have to do this, if I don't the bridgetable builds 340 000 000 rows and is very slow.

I hope that this will create a lot less records.

Re: Working with overlapping date-ranges

arasaraja_cts — Tue, 16 Nov 2021 12:23:25 GMT

For the benefit of all, can you please share what HIC explained to you ?

Because I have the same problem which you had after following the script. Trips that are split over 2 days are not getting captured.