topic Re: Best calculation of time difference when the data is in multiple rows of a table in QlikView

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

Tue, 28 Jul 2015 14:33:49 GMT

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

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

Anonymous — Tue, 28 Jul 2015 14:40:12 GMT

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

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

lironbaram — Tue, 28 Jul 2015 14:41:58 GMT

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

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

Tue, 28 Jul 2015 15:18:20 GMT

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

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

Anonymous — Tue, 28 Jul 2015 17:12:52 GMT

hi wallace,

what are the values in Test TOT Actual column.

are they mins ?

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

Anonymous — Tue, 28 Jul 2015 17:13:50 GMT

how did you arrive the result for the column

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

Tue, 28 Jul 2015 17:21:30 GMT

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!

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

maxgro — Tue, 28 Jul 2015 17:26:44 GMT

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

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

Anonymous — Tue, 28 Jul 2015 17:40:54 GMT

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

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

Tue, 28 Jul 2015 18:11:28 GMT

Hi Neetha,

This is a great suggestion - thank you!

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

Much appreciation!

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

Tue, 28 Jul 2015 18:12:11 GMT

Hi Massimo,

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

Much appreciation!

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

maxgro — Tue, 28 Jul 2015 18:24:01 GMT

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;

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

Tue, 28 Jul 2015 18:34:00 GMT

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.