topic Re: Help needed with times please in QlikView

Help needed with times please

timsaddler — Thu, 06 Mar 2014 11:13:38 GMT

hello
Can someone advise how i work out the elapsed time from this data

A.collect_finish

20140214 10:56:00

B.deliver

20140214 12:41:17

I first strip the time "right(collect_finish,8)" out to give

10:56:00

then "right(deliver_start,8)" out to give

12:41:17

interval(right(collect_finish,8),right(deliver_start,8)) as #ActTravelTime,

This returns 1.8222 which i guess is hours - converted = 109.333 mins?

Answer should be 105 mins 17 secs

Re: Help needed with times please

tresB — Thu, 06 Mar 2014 11:17:35 GMT

Have you tried like :

Interval(time1-time2 , 'mm') as MinDiff

Re: Help needed with times please

MK_QSL — Thu, 06 Mar 2014 11:19:23 GMT

=INTERVAL(TIME(Timestamp#('20140214 12:41:17','YYYYMMDD hh:mm:ss')) -

TIME(Timestamp#('20140214 10:56:00','YYYYMMDD hh:mm:ss'))

,'mm:ss')

Re: Help needed with times please

jonathandienst — Thu, 06 Mar 2014 11:21:13 GMT

This should do it:

Interval(TimeStamp#(collect_finish, 'YYYYMMDD hh:mm:ss') - TimeStamp#(deliver_start, 'YYYYMMDD hh:mm:ss'), 'hh:mm:ss')

HTH

Jonathan

Re: Help needed with times please

MarcoWedel — Thu, 06 Mar 2014 11:21:47 GMT

=Interval(Timestamp#('20140214 12:41:17', 'YYYYMMDD hh:mm:ss')-Timestamp#('20140214 10:56:00', 'YYYYMMDD hh:mm:ss'), 'mm:ss')

regards

Marco

Re: Help needed with times please

Thu, 06 Mar 2014 11:22:30 GMT

Data:

LOAD * INLINE [

Collect,Deliver

"20140214 10:56:00","20140214 12:41:17"

];

DataFinal:

LOAD

Interval(Deliver - Collect,'hh:mm:ss')*24*60 as TimeDiffNumberFormat,

Interval(Deliver - Collect,'hh:mm:ss') as TimeDiffTimeFormat

RESIDENT Data;

DROP TABLE Data;

Re: Help needed with times please

timsaddler — Thu, 06 Mar 2014 11:37:56 GMT

Thanks guys for your prompt response - saved me hours messing about...