Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

Time between Two timestamps

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

9 Replies
sreenivas
Creator III
Creator III

=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')

gerhardl
Creator II
Creator II
Author

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')

sreenivas
Creator III
Creator III

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.

gerhardl
Creator II
Creator II
Author

Sorry not sure what you mean.

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

sreenivas
Creator III
Creator III

can you add qvw.

gerhardl
Creator II
Creator II
Author

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.

sreenivas
Creator III
Creator III

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

sreenivas
Creator III
Creator III

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')

gerhardl
Creator II
Creator II
Author

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.

G