Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=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')
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')
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.
Sorry not sure what you mean.
This is an expression in a straight table I am using.
can you add qvw.
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.
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
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')
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