Discussion Board for collaboration related to QlikView App Development.
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
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.
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
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.
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.
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
125, 132 WeekendShift2