Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

solomon_musayev
New Contributor III

Correcting Fields Based on Previous Records

Hello,

I have a table like below and I am working on a PeriOp application that calculate the total minutes a room is utilized.

There are three shifts in day with the first shift being 07:00-15:30

The field Turn around time calculates the difference in minutes from Out Time of the previous Records and the In Time of the next Record.  For Example the third row In time is 14:46 and the Out time of the Second Row is 13:28 therefore 14:46 - 13:28 = 78 minutes of Turn around time which is recorded in the third row.

DayRoomShiftIn Room TimeOut TimeTurn Around Time
1/1/2017
OR#5
Third
00:43
1:38
0
1/1/2017
OR#5
First
11:00
13:28
562
1/1/2017
OR#5
First
14:46
15:30
78

The issue that I am having and hoping that someone can help me with, is that according to Operating Logic, The first Record of the First Shift (in this case 2nd record in the table) Should have a turn around time of 0.  Instead the table is calculating the Turn Around Time from the Third Shift which is incorrect.

I need an output table to show the following ( Second row, which is the first record in the First shift should have a Turn Around Time of 0).

DayRoomShiftIn Room TimeOut TimeTurn Around Time
1/1/2017
OR#5
Third
00:43
1:38
0
1/1/2017
OR#5
First
11:00
13:28
0
1/1/2017
OR#5
First
14:46
15:30
78

Please help

1 Reply
mato32188
Contributor III

Re: Correcting Fields Based on Previous Records

Hi Salamon,

I would propose to create flag field in script, that would track the shift change.

Load *,

         if(@ShiftChange=1,0,InRoomTime-Previous(OutTime) as TurnAround;

Load

Day,

Room,

Shift,

InRoomTime,

OutTime,

If(Shift=Previous(Shift),0,1) as @ShiftChange

FROM ...

BR

Martin

Community Browser