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

[Anonymous]

take min(PIR_TIME) max(POR_TIME) and calculate the time duratiion

[lironbaram]

hi

if you want to calculate it at the script level

you can use

Load *

      if(previous(Surgeon)=Surgeon,PIR_TIME-previous(POR_TIME) ) As POR to next PIR

Resident XXXX

ORDER BY SURGEON ,PROCUDRE_DATE_DT ,CASE_CONFIRMATION_NUM

[#4]

Neeti and Liron,

Thank you very much for your feedback.

I tried both of your suggestions (per below) but am still not getting the correct time (in the Test TOT Actual column):

PROCEDURE_DATE_DT	PIR_TIME	POR_TIME	Test TOT neetha	Test TOT liron	Test TOT Actual
07/20/2015	07:15	08:03	-182	-48
07/20/2015	07:36	09:36	-518	-120	-27
07/20/2015	09:11	10:17	-349	-66	-25
07/20/2015	09:59	13:20	23	-201	-18
07/20/2015	13:05	15:00	-306	-115	-15
07/20/2015	14:40	16:14	-	-94	-20
07/20/2015	16:16	18:11	-	-115	2

[Anonymous]

hi wallace,

what are the values in Test TOT Actual column.

are they mins ?

[Anonymous]

how did you arrive the result for the column

[#7]

For the first case of the day, there is not calculation.

For the [Test TOT Actual] second case of the day, I start at row 2 and subtract PIR_TIME 7:36 from POR_TIME in row 1 which is 8:03.

This continues for each of the cases in sequential order.

It may appear odd that the cases look like they overlap - in some cases, one physician is doing multiple procedures at one time.

I've done a recursive technique in SQL before to address this; however, am not sure how to "loop" through in Qlik.

Appreciate your help!

[maxgro]

I think Liron already suggested a possible answer

Another could be (if CASE_CONF.... are progressive values) a join of the table with the same table (same surgeon, procedure_date, CASE_CONF = CASE_CONF +1) so you have pir_time and previuos row por_time on the same row

If you can post some data, qvd or excel or qvw...........

[Anonymous · Accepted solution]

Hi Wallace,

Try below solution:

Data:

load * inline

[

PROCEDURE_DATE_DT,PIR_TIME,POR_TIME 

07/20/2015,07:15,08:03

07/20/2015,07:36,09:36

07/20/2015,09:11,10:17

07/20/2015,09:59,13:20

07/20/2015,13:05,15:00

07/20/2015,14:40,16:14

07/20/2015,16:16,18:11

];

LOAD *,

if(PROCEDURE_DATE_DT = Peek(PROCEDURE_DATE_DT ),  Interval(PIR_TIME-Previous(POR_TIME) ,'')) as TimeBetweentransactions

Resident Data;

hope it helps!

Regards

Neetha

[#10]

Hi Neetha,

This is a great suggestion - thank you!

I just need to clean up some synthetic keys that are being generated!

Much appreciation!