Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Split rowvalue into multiple rows

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;

Labels (2)
1 Reply
Highlighted
Creator III
Creator III

Re: Split rowvalue into multiple rows

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,...]