Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gouthamkk
Contributor

Creating Date Difference for same Dimension

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

1 Solution

Accepted Solutions

Re: Creating Date Difference for same Dimension

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;


Capture.PNG

2 Replies

Re: Creating Date Difference for same Dimension

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;


Capture.PNG

MVP
MVP

Re: Creating Date Difference for same Dimension

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
;

Community Browser