topic Re: Duration between time stamps in QlikView

Duration between time stamps

Anonymous — Tue, 15 Sep 2015 21:01:05 GMT

Hi QV expert!

I'd like to calculate the duration in hour between two time stamps shown below in the load script. Can someone help me with that?

Thanks

Job ID	Time Started	Time Completed	Duration(hr) (example)
1	August 16, 2015 at 9:54 AM	August 19, 2015 at 9:21 PM	60.2
2	August 20, 2015 at 5:55 PM	August 21, 2015 at 3:44 AM	10
3	September 8, 2015 at 4:49 PM	September 8, 2015 at 5:02 PM	0.1

Re: Duration between time stamps

sunny_talwar — Tue, 15 Sep 2015 21:14:43 GMT

Not sure if this is what you want, but I got different value for Job 1:

If the values look good. I used the following script:

SET TimestampFormat='MMM DD YYYY h:mmTT';

Table:

LOAD *,

Interval(Timestamp#([Time Completed]) - Timestamp#([Time Started]), 'hh:m') as Difference;

LOAD * Inline [

Job ID, Time Started, Time Completed, Test

1, Aug 16 2015 9:54AM, Aug 19 2015 9:21PM, 60.2

2, Aug 20 2015 5:55PM, Aug 21 2015 3:44AM, 10

3, Sep 8 2015 4:49PM, Sep 8 2015 5:02PM, 0.1

];

Re: Duration between time stamps

maxgro — Tue, 15 Sep 2015 21:24:31 GMT

see attachment

expression is

Interval(

(

Date#(replace([Time Completed], 'at ', ''), 'MMMM DD, YYYY h:mm tt')

Date#(replace([Time Started], 'at ', ''), 'MMMM DD, YYYY h:mm tt')

)

,'hh')

Re: Duration between time stamps

MarcoWedel — Tue, 15 Sep 2015 22:09:33 GMT

Hi,

another solution could be.

LOAD *,
     Num(([Time Completed]-[Time Started])*24,'0.0') as [Duration(hr)];
LOAD [Job ID], 
     Timestamp(Timestamp#(SubField([Time Started],'at',1)&SubField([Time Started],'at',2),'MMMM DD, YYYY  h:mm TT')) as [Time Started], 
     Timestamp(Timestamp#(SubField([Time Completed],'at',1)&SubField([Time Completed],'at',2),'MMMM DD, YYYY  h:mm TT')) as [Time Completed]
FROM [https://community.qlik.com/thread/180887] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco