topic Creating Date Difference for same Dimension in QlikView

Creating Date Difference for same Dimension

gouthamkk — Wed, 27 Apr 2016 18:05:24 GMT

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

Re: Creating Date Difference for same Dimension

sunny_talwar — Wed, 27 Apr 2016 18:15:45 GMT

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;

Re: Creating Date Difference for same Dimension

johnw — Wed, 27 Apr 2016 18:16:40 GMT

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
;