topic Subtract with previous record in a different column in QlikView

Subtract with previous record in a different column

MSK — Wed, 31 Aug 2022 15:50:16 GMT

Hi,

I'm facing an issue with previous functionality.

Below is the sample I have. And I want the Turnover Time as the result column.

The logic should be In Time Column 2nd row minus Out Time Column 1st Row, that gives me Turnover as -18
the next result should be In Time 3rd column minus Out Time 2nd Column should give me -72 and so on...

MRN	OR	Schedueld Time	In Time	Out Time	Turnover Time
29402	2	7:30 AM	7:23 AM	7:45 AM
30771	3	7:45 AM	7:27 AM	9:23 AM	-18
30836	2	8:15 AM	8:11 AM	9:15 AM	-72
30136	2	9:30 AM	9:26 AM	9:57 AM	11
30766	3	9:00 AM	9:43 AM	10:53 AM	-14
30736	2	10:40 AM	10:12 AM	10:45 AM	-41
30801	2	11:10 AM	10:55 AM	11:21 AM	10
30640	3	10:00 AM	11:11 AM	12:19 PM	-10
30639	2	12:00 PM	11:32 AM	11:51 AM	-47
30741	2	11:30 AM	12:00 PM	12:32 PM	9
30249	3	11:00 AM	12:28 PM	1:05 PM	-4
30643	2	12:35 PM	12:41 PM	1:00 PM	-24
30394	2	12:15 PM	1:13 PM	1:39 PM	13

I tried previous function along with row() as well and grouping them in ASC order. Doesn't give me expected result. Any help is appreciated.

Re: Subtract with previous record in a different column

Aurelien_Martinez — Wed, 31 Aug 2022 16:43:08 GMT

Hi,

If you want to do that in script :

data:
LOAD
MRN,
OR,
Time#([Schedueld Time], 'hh:mm') as [Schedueld Time],
Time#([In Time], 'hh:mm') as [In Time],
Time#([Out Time], 'hh:mm') as [Out Time]
Inline [
MRN, OR , Schedueld Time, In Time, Out Time
29402, 2, 7:30, 7:23, 7:45,
30771, 3, 7:45, 7:27, 9:23
30836, 2, 8:15, 8:11, 9:15
30136, 2, 9:30, 9:26, 9:57
30766, 3, 9:00, 9:43, 10:53
30736, 2, 10:40, 10:12, 10:45
30801, 2, 11:10, 10:55, 11:21
30640, 3, 10:00, 11:11, 12:19
30639, 2, 12:00, 11:32, 11:51
30741, 2, 11:30, 12:00, 12:32
30249, 3, 11:00, 12:28, 13:05
30643, 2, 12:35, 12:41, 13:00
30394, 2, 12:15, 13:13, 13:39
];

LOAD
MRN,
OR,
[Schedueld Time],
[In Time],
[Out Time],
Peek([Out Time]) as peek,
Interval([In Time] - Peek([Out Time]), 'm') as [Turnover Time]
Resident data
//Order by
// [Schedueld Time] asc
;

DROP Table data;

Aurélien

Re: Subtract with previous record in a different column

MSK — Wed, 31 Aug 2022 17:11:11 GMT

Tried the bottom script, doesn't give me same results.

MRN, OR are coming from different table.

attached is the actual fields from my table. The timestamp format is different here etc. Maybe the way I'm joining my tables is not giving me right results?

Re: Subtract with previous record in a different column

BrunPierre — Wed, 31 Aug 2022 18:24:42 GMT

@MSK You can also do it differently with if function.

Load *,

if(RecNo()=2,Interval([In Time]- peek([Out Time]),'m'),
Interval(([In Time])- Previous([Out Time]),'m')) as [Turnover Time];

LOAD RN,
[OR ],
time([Schedueld Time]) as [Schedueld Time],
time([In Time]) as [In Time],
time([Out Time]) as [Out Time]
FROM SourceTable:

Re: Subtract with previous record in a different column

MSK — Wed, 31 Aug 2022 18:16:14 GMT

I still dont get the same result

My backend script w/o changes and the other image is when I use your expression (with arrow).

Re: Subtract with previous record in a different column

BrunPierre — Wed, 31 Aug 2022 21:30:24 GMT

@MSK maybe it's nothing, nevertheless, you do realize there's a filter on PhyTurnover table. I'll suggest you place the IF syntax in the final table.

Re: Subtract with previous record in a different column

MSK — Wed, 31 Aug 2022 23:44:35 GMT

Attached is the actual data.

My timestamps are different... I'm looking for Operative Start date time - previous Operative stop date time

Re: Subtract with previous record in a different column

MSK — Wed, 31 Aug 2022 23:47:07 GMT

Tried replacing my if statement with yours, I get all positive values.