Calculate Time Difference for a Field

[microwin88x]

Hello,

I have the following table:

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

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 · Accepted solution]

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]

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

[http://community.qlik.com/thread/154108]

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