Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
davidteke
Contributor II
Contributor II

Duration/interval time between date/time values in same field

Hi All,

Please assist me to crack this one: a logic to calculate the duration/interval time between first and the previous time in one field. The below is how my data looks like for each session.

LOAD *  INLINE [

    ID,Date and Time

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

1 Solution

Accepted Solutions
sunny_talwar

May be try this

SET TimestampFormat='MM/DD/YYYY h:mm:ss';

Table:

LOAD *  INLINE [

    ID,Date and Time

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

FinalTable:

LOAD *,

  Interval(If(ID = Previous(ID), [Date and Time] - Previous([Date and Time]), 0), 'D h:mm:ss') as Difference

Resident Table

Order By ID, [Date and Time];

DROP Table Table;


Capture.PNG

View solution in original post

2 Replies
sunny_talwar

May be try this

SET TimestampFormat='MM/DD/YYYY h:mm:ss';

Table:

LOAD *  INLINE [

    ID,Date and Time

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

FinalTable:

LOAD *,

  Interval(If(ID = Previous(ID), [Date and Time] - Previous([Date and Time]), 0), 'D h:mm:ss') as Difference

Resident Table

Order By ID, [Date and Time];

DROP Table Table;


Capture.PNG

View solution in original post

davidteke
Contributor II
Contributor II
Author

Thank you Sunny

That work perfectly!! I re did with the peek() and I still got the same results . See the below:

SET TimestampFormat='DD/MM/YYYY h:mm:ss';

table1:

LOAD *  INLINE [

    ID,DT

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

LOAD *,

Interval(DT - if(Peek(ID)=ID,Peek(DT),DT)) as Interval

Resident table1

Order By ID,DT;

DROP Table table1;

Capture.PNG