Best calculation of time difference when the data is in multiple rows of a table

[#1]

Hello,

I'd like to get some advice on how best to calculate time difference when the data is in multiple rows of a table.

In the example below (POR to next PIR) is easy to calculate in Excel by setting the later PIR_TIME minus the earlier POR_TIME for the surgeon.

However, when I've tried to recreate this in Qlikview, I keep getting errors.

I've done something similar to this in SQL using a CTE but am not as well-versed as how to handle this dynamically in Qlikview - in the load script, functions, etc.

Welcome your responses!

Regards,

Karen

PROCEDURE_DATE_DT	CASE_CONFIRMATION_NUM	SURGEON	PIR_TIME	POR_TIME	POR to next PIR
07/20/2015	1	Dr. B	07:36	09:36
07/20/2015	2	Dr. B	09:59	13:20	23
07/20/2015	3	Dr. B	13:05	15:00	-15
07/20/2015	4	Dr. B	14:40	16:14	-20

[#2]

Hi Massimo,

This is a good suggestion - I will try to do the table join and re-post!

Much appreciation!

[maxgro]

z:

load field1, field2, Evaluate(pir) as pir, Evaluate(por) as por inline [

field1, field2, pir, por

a,1, 1/24, 2/24

a,2, 3/24, 4/24

a,3, 5/24, 6/24

b,1, 17/24, 18/24

b,2, 20/24, 22/24

b,3, 23/24, 0.99999

];

left join (z)

load

field1, field2+1 as field2, por as por_prevrow

Resident z;

[#4]

Hi Massimo,

Thank you very much for your example.

My only caveat is that the CASE_CONFIRMATION_NUMBER may not be sequential, hence cannot assume it is +1.

I will work on tweaking the LEFT join.