topic Re: Calculate Time Difference for a Field in QlikView

Calculate Time Difference for a Field

microwin88x — Wed, 25 Feb 2015 14:10:11 GMT

Hello,

I have the following table:

What I need by Script is to add one more column with the times between PHASE 1 and 2, PHASE 2 and 3.

I could have more PHASES than just 3, so it should be dynamic.

Do you know any whay to do this?

Thank you!

MK_QSL — Wed, 25 Feb 2015 14:20:58 GMT

Temp:

Load

Text(ID) as ID,

PHASE,

TimeStamp(Timestamp#(OPERATION_DTTM, 'YYYY/MM/DD hh:mm:ss TT')) as OPERATION_DTTM

Inline

[

ID, PHASE, OPERATION_DTTM

001, 1, 2015/02/23 10:15:00 AM

001, 2, 2015/02/23 10:30:25 AM

001, 3, 2015/02/23 11:55:53 AM

002, 1, 2015/02/23 10:15:00 AM

002, 2, 2015/02/23 10:30:25 AM

002, 3, 2015/02/23 11:55:53 AM

002, 4, 2015/02/23 11:58:00 AM

003, 1, 2015/02/23 10:30:25 AM

003, 2, 2015/02/23 11:55:53 AM

];

Final:

Load

ID,

PHASE,

OPERATION_DTTM,

If(ID = Previous(ID),Interval(OPERATION_DTTM - Previous(OPERATION_DTTM),'hh:mm:ss'), 0) as Difference

Resident Temp

Order By ID, PHASE;

Drop Table Temp

maxgro — Wed, 25 Feb 2015 14:40:47 GMT

LOAD ID,

PHASE,

OPERATION_DTTM,

interval(Timestamp#(OPERATION_DTTM, 'YYYY/MM/DD hh:mm:ss tt') - Timestamp#(peek(OPERATION_DTTM), 'YYYY/MM/DD hh:mm:ss tt' )) as diff2

FROM

(html, codepage is 1252, embedded labels, table is @1);