Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a client that works in shifts.
There are 2 data tables that I will use, 1 for the shift times and 1 for the actual times worked.
I need to determine
1: who worked in which shift
2: how many total hours were worked per shift
See my example data below. Note that the Shifts table does not have dates, and the Transaction table has date and time in a single column.
Table1: (Shifts table)
ShiftId ShifName ShiftStartTime ShiftEndTime
1 Shift1 05:00 09:00
2 Shift2 09:00 21:00
3 Shift3 21:00 05:00
Table2: (Transaction table)
StartDate EndDate EmployeeId
2014-03-03 04:00:00 2014-03-03 06:00 Emp01
2014-03-03 16:30:00 2014-03-04 22:00 Emp02
2014-03-03 04:15:00 2014-03-03 11:30 Emp03
2014-03-03 05:15:00 2014-03-04 01:00 Emp04
Have you tried interval match ?
The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals.
It must be placed before a Load or Select (SQL)statement that loads the intervals. The field containing the discrete data points (Time in the example below) must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix does not by itself read this field from the database table. The prefix transforms the loaded table of intervals to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point and interval.
The intervals may be overlapping and the discrete values will be linked to all matching intervals.
The general syntax is:
intervalmatch (matchfield) (loadstatement | selectstatement )
matchfield is the field containing the discrete numeric values to be linked to intervals.
loadstatement or selectstatement must result in a two-column table, where the first field contains the lower limit of each interval and the second field contains the upper limit of each interval. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).
There is also an extended syntax of IntervalMatch including one or several additional key fields. See IntervalMatch (Extended Syntax).
Example:
In the two tables below, the first one defines the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch prefix it is possible to logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.
OrderLog | ||
Start | End | Order |
01:00 | 03:35 | A |
02:30 | 07:58 | B |
03:04 | 10:27 | C |
07:23 | 11:43 | D |
EventLog | ||
Time | Event | Comment |
00:00 | 0 | Start of shift 1 |
01:18 | 1 | Line stop |
02:23 | 2 | Line restart 50% |
04:15 | 3 | Line speed 100% |
08:00 | 4 | Start of shift 2 |
11:43 | 5 | End of production |
First load the two tables as usual, then link the field Time to the time intervals defined by the fields Start and End:
SQL SELECT * FROM OrderLog;
SQL SELECT * FROM Eventlog;
IntervalMatch ( Time ) SQL SELECT Start, End FROM OrderLog;
The following table box can now be created in QlikView:
Tablebox | |||||
Time | Event | Comment | Order | Start | End |
00:00 | 0 | Start of shift 1 | - | - | - |
01:18 | 1 | Line stop | A | 01:00 | 03:35 |
02:23 | 2 | Line restart 50% | A | 01:00 | 03:35 |
04:15 | 3 | Line speed 100% | B | 02:30 | 07:58 |
04:15 | 3 | Line speed 100% | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | D | 07:23 | 11:43 |
11:43 | 5 | End of production | D | 07:23 | 11:43 |
Have you tried interval match ?
The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals.
It must be placed before a Load or Select (SQL)statement that loads the intervals. The field containing the discrete data points (Time in the example below) must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix does not by itself read this field from the database table. The prefix transforms the loaded table of intervals to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point and interval.
The intervals may be overlapping and the discrete values will be linked to all matching intervals.
The general syntax is:
intervalmatch (matchfield) (loadstatement | selectstatement )
matchfield is the field containing the discrete numeric values to be linked to intervals.
loadstatement or selectstatement must result in a two-column table, where the first field contains the lower limit of each interval and the second field contains the upper limit of each interval. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).
There is also an extended syntax of IntervalMatch including one or several additional key fields. See IntervalMatch (Extended Syntax).
Example:
In the two tables below, the first one defines the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch prefix it is possible to logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.
OrderLog | ||
Start | End | Order |
01:00 | 03:35 | A |
02:30 | 07:58 | B |
03:04 | 10:27 | C |
07:23 | 11:43 | D |
EventLog | ||
Time | Event | Comment |
00:00 | 0 | Start of shift 1 |
01:18 | 1 | Line stop |
02:23 | 2 | Line restart 50% |
04:15 | 3 | Line speed 100% |
08:00 | 4 | Start of shift 2 |
11:43 | 5 | End of production |
First load the two tables as usual, then link the field Time to the time intervals defined by the fields Start and End:
SQL SELECT * FROM OrderLog;
SQL SELECT * FROM Eventlog;
IntervalMatch ( Time ) SQL SELECT Start, End FROM OrderLog;
The following table box can now be created in QlikView:
Tablebox | |||||
Time | Event | Comment | Order | Start | End |
00:00 | 0 | Start of shift 1 | - | - | - |
01:18 | 1 | Line stop | A | 01:00 | 03:35 |
02:23 | 2 | Line restart 50% | A | 01:00 | 03:35 |
04:15 | 3 | Line speed 100% | B | 02:30 | 07:58 |
04:15 | 3 | Line speed 100% | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | D | 07:23 | 11:43 |
11:43 | 5 | End of production | D | 07:23 | 11:43 |
I have but I think I am missing something. Here is my code and the resulting table structure.
Intervals:
LOAD Name,
Time(StartTime) as [Shift Start Time],
Time(EndTime) as [Shift End Time];
SQL SELECT Name,
StartTime,
EndTime
FROM ColossusUat.dbo.Shift;
Events:
LOAD Time(StartDate) as [Start Time],
Time(EndDate) as [End Time],
DriverId;
SQL SELECT StartDate,
EndDate,
DriverId
FROM ColossusUat.dbo.Trip;
IntervalMatch:
IntervalMatch ([End Time])
LOAD [Shift Start Time],
[Shift End Time]
Resident Intervals;
Hi Joggie,
This can be achieved by Interval-match concept. Please see the attachment.
Thanks,
AS
Intervalmatch works like a charm, my problem was the date and time formats
Thanks to all
IntervalMatch:
IntervalMatch ([Start Time])
LOAD [Shift Start Time],
[Shift End Time]
Resident Intervals;
Left Join (Events) Load * Resident Intervals;
Drop Table Intervals;
Hello Amit,
I have a question :
Your tables are round at the corners. How do you succeed it ?
In my Qlikview I do not see you rounding info :
The red box does not exist in my Table Box in Layout tab :
Do you know how to make it appear in my case ?
Thanks
Kosmas
Answered by Massimo Grossi :