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

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

];

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

sunny_talwar — Fri, 26 May 2017 09:27:55 GMT

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;

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

Anonymous — Fri, 26 May 2017 12:53:30 GMT

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;