Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
my goal is to split machine events at shift and day changes.
I have a list of machine events that are defined by an eventname and a timestamp for the beginning of the event. For analyzing the duration of the events I calculated the time to the next change of events. These are allocated to the event start. Some events last during a shiftchange/midnight and are therefore allocated to the previous shift/date and distort my analysis. How can I split these events to all affected shifts/days?
Thanks!
Original data
rowID | timestamp | eventname |
1 | 03/04/2020 16:30:22 | machine working |
2 | 03/04/2020 23:55:18 | error |
3 | 04/04/2020 03:17:51 | machine working |
current state
rowID | timestamp | eventname | event_start | event_end | event_duration_min | shift |
1 | 03/04/2020 15:46:13 | machine working | 03/04/2020 16:30:22 | 03/04/2020 23:55:18 | 445 | afternoon |
2 | 03/04/2020 16:15:35 | error | 03/04/2020 23:55:18 | 04/04/2020 03:17:51 | 203 | night |
3 | 03/04/2020 16:30:22 | machine working | 04/04/2020 03:17:51 | 04/04/2020 04:34:18 | 76 | night |
target state
rowID | eventname | event_start | event_end | event_duration_min | shift |
1 | machine working | 03/04/2020 16:30:22 | 03/04/2020 20:59:59 | 270 | afternoon |
2 | machine working | 03/04/2020 21:00:00 | 03/04/2020 23:55:18 | 175 | night |
3 | error | 03/04/2020 23:55:18 | 03/04/2020 23:59:59 | 5 | night |
4 | error | 04/04/2020 00:00:00 | 04/04/2020 03:17:51 | 198 | Night |
5 | machine working | 04/04/2020 03:17:51 | 04/04/2020 04:34:18 | 76 | night |
Code so far
Start:
LOAD
rowid,
"timestamp",
"timestamp"as event_start
FROM [lib://AttachedFiles/Laufzeit.xml]
(XmlSimple, table is [data/slavestate]);
End:
Load
rowid,
event_start,
Peek(event_start, rowid, 'Start') as event_end
resident Start
order By rowid,event_start;
Duration:
Load
rowid,
event_start,
event_end,
(event_end-event_start)*24*60 as event_duration_min
if(hour([event_start])>=05 and hour([event_startt])<13, 'Morning',
if(hour([event_start])>=13 and hour([event_start])<21,'Afternoon', 'Night')) as Shift
resident End
order By rowid, event_start;
start by creating one big calendar containing all minutes/seconds (depending on how granular you want your app)
(define afternoon, night,Night, morning in that table as well, not in your events table)
after that, well, typical case for an interval match : https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...
(you are going to match your minutes table with your events (Start-End) times.
finally either keep the whole "calendar" (that will allow you to pick any minute from any day and analyse the "current state" of all machines)
or just keep the parts that you need (the shift info) [join, drop fields, drop tables,...]