Creating Date Difference for same Dimension

[gouthamkk]

HI,

i have 2 fields and below is sample data

Actual Table

ID          Time              

12A         12/15/2015 5:04:00 AM

12A          2/15/2015 5:04:25 AM

12A          2/15/2015 5:04:40 AM

12A          2/15/2015 5:05:00 AM

13A          2/15/2015 5:05:10 AM

13A          2/15/2015 5:06:00 AM

13A          2/15/2015 5:07:00 AM

13A          2/15/2015 5:07:40 AM

I would like to achieve in scripting as below

Output Reqested:

ID          Time                                        Diff

12A         12/15/2015 5:04:00 AM               0

12A          2/15/2015 5:04:25 AM               25

12A          2/15/2015 5:04:40 AM               15

12A          2/15/2015 5:05:00 AM               20

-------------------------------------------------------------------------------Since a new ID begin the Diff should become 0

13A          2/15/2015 5:05:10 AM               0

13A          2/15/2015 5:06:00 AM               50

13A          2/15/2015 5:07:00 AM               60

13A          2/15/2015 5:07:40 AM               40

Basically it is taking the difference of time in seconds from second record to the row above it and once the ID is changing the Diff should be 0. I can get this in the fron end in a chart. But would like to write the code in backed(QlikScripting) and store in a table

Please Advise.

Thanks

Kumar

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

ID,           Time             

12A,          2/15/2015 5:04:00 AM

12A,          2/15/2015 5:04:25 AM

12A,          2/15/2015 5:04:40 AM

12A,          2/15/2015 5:05:00 AM

13A,          2/15/2015 5:05:10 AM

13A,          2/15/2015 5:06:00 AM

13A,          2/15/2015 5:07:00 AM

13A,          2/15/2015 5:07:40 AM

];

FinalTable:

LOAD *,

  If(ID = Peek('ID'), Round((Time - Peek('Time')) * 24*3600), 0) as Diff

Resident Table

Order By ID, Time;

DROP Table Table;

[johnw]

I believe this would work.

LEFT JOIN (Table)
LOAD
ID
,Time
,interval(if(ID=previous(ID),Time-previous(Time),0)) as Diff
RESIDENT Table
ORDER BY ID, Time
;