Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gerhardl
Contributor 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

Tags (1)
9 Replies
sreenivas
Contributor III

Re: Time between Two timestamps

=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
Contributor II

Re: Time between Two timestamps

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
Contributor III

Re: Time between Two timestamps

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
Contributor II

Re: Time between Two timestamps

Sorry not sure what you mean.

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

sreenivas
Contributor III

Re: Time between Two timestamps

can you add qvw.

gerhardl
Contributor II

Re: Time between Two timestamps

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
Contributor III

Re: Time between Two timestamps

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
Contributor III

Re: Time between Two timestamps

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
Contributor II

Re: Time between Two timestamps

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

Community Browser