topic Re: Time between Two timestamps in QlikView

Time between Two timestamps

gerhardl — Thu, 03 Jan 2013 12:13:41 GMT

Hi There,

I'm having problems with time format.

In my load script I have:

Timestamp(Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss')) as [Log Date & Time],

Timestamp(Timestamp#( [Decision Date], 'DD-MMM-YYYY hh:mm:ss')) as [Decision Date & Time],

In an expression (not in the load script) I then want to calculate my avergae "Process Time", which is the average time difference the two.

I get the wrong values whenever the time taken is more than 24 hours.

For instance, if log date is 2012/12/30 3:10:00 PM and Decision Date is 2012/12/31 4:15:00 PM, I get an answer of 1:05:00, when it should be 25:05:00

I know I should use interval but cannot get the format of the expression right.

Please help,

Gerhard

Re: Time between Two timestamps

sreenivas — Thu, 03 Jan 2013 12:23:20 GMT

=Interval(Date(Date#('2012/12/31 4:15:00 PM','YYYY/MM/DD h:mm:ss [ttt]'))-Date(Date#('2012/12/30 3:10:00 PM','YYYY/MM/DD h:mm:ss [ttt]')),'hh:mm:ss')

Re: Time between Two timestamps

gerhardl — Thu, 03 Jan 2013 12:28:03 GMT

I tried this, but it returns NULL value:

=Interval(Date(Date#([Decision Date & Time],'YYYY/MM/DD h:mm:ss [ttt]'))-

Date(Date#([Log Date & Time],'YYYY/MM/DD h:mm:ss [ttt]')),'hh:mm:ss')

Re: Time between Two timestamps

sreenivas — Thu, 03 Jan 2013 12:30:55 GMT

Actually the above will work in the text object only. If you want to use by Field names then Try in the Straight table By givng this expression.

Because that fields contains more than one value.

Re: Time between Two timestamps

gerhardl — Thu, 03 Jan 2013 12:32:21 GMT

Sorry not sure what you mean.

This is an expression in a straight table I am using.

Re: Time between Two timestamps

sreenivas — Thu, 03 Jan 2013 12:33:12 GMT

can you add qvw.

Re: Time between Two timestamps

gerhardl — Thu, 03 Jan 2013 12:35:45 GMT

Sorry I can't - will have to create a sample file of this - plus I have Personal Edition so can't look at a file you then return to me.

But these are my document date formats:

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';

and the first comment shows how I loaded these fields.

Re: Time between Two timestamps

sreenivas — Thu, 03 Jan 2013 12:39:08 GMT

In that Fields(Columns) [Decision Date & Time],[Log Date & Time]

how many rows are there.

What is the dimension you are giving?

Add sample data for clear your doubt

Re: Time between Two timestamps

sreenivas — Thu, 03 Jan 2013 12:46:21 GMT

Add in the script

Load * Inline [

Decision Date & Time,Log Date & Time

2012/12/31 4:15:00 PM,2012/12/30 2:10:00 PM

2012/12/29 3:10:00 PM,2012/12/28 3:10:00 PM

2012/12/28 5:10:00 PM,2012/12/27 4:10:00 PM

2012/12/25 6:10:00 PM,2012/12/22 9:10:00 PM

2012/12/26 2:10:00 PM,2012/12/23 6:10:00 PM

2012/12/27 1:10:00 PM,2012/12/26 8:10:00 PM

];

Then reload the Data And Take a Listbox and Go to Expression wite this Expresssion

=Interval(Date(Date#([Decision Date & Time],'YYYY/MM/DD h:mm:ss [ttt]'))-Date(Date#([Log Date & Time],'YYYY/MM/DD h:mm:ss [ttt]')),'hh:mm:ss')

Re: Time between Two timestamps

gerhardl — Thu, 03 Jan 2013 13:09:03 GMT

I think you misunderstood my requirement. I have a million records with different timestamps for those two fields.

I wanted to do this without changing the load scripts and QVD, but this works now that I have this in my script:

interval(Timestamp( Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') - Timestamp(Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss')) ),'hh:mm:ss') as Process_Time,

Thanks for your input.