Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need help with a script that is already doing my head in. I use application extract with the following two fields:
[Log Date]
[Decision Date]
Both are in the following format (in a delimited text file):
27-MAY-2011 14:46:28 (i.e. DD-MMM-YYYY hh:mm:ss)
Using these 2 fields I determine the process time of an application. At the moment I determine this in DAYS, but I need to do so in hours. Please can someone help me to amend the below script so it takes the hh:mm:ss into consideration and so my [Process Time] field shows in hours:
Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],
Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY')) as [Decision Date],
weekday(Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY'))) as [Decision Day],
month(Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY'))) as [Decision Month],
year(Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY'))) as [Decision Year],
if([Decicion Result]='A','Approved','Declined') as [Decision Result],
if(Date#( left( [Log Date],11), 'DD-MMM-YYYY') =date#( left([Decision Date],11),'DD-MMM-YYYY'), 'Same Day', date#( left([Decision Date],11),'DD-MMM-YYYY')-Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Process Time],
Thanks in advance,
Gerhard
Hello Gerhardl
try this:
if(Date#([Log Date], 'DD-MMM-YYYY hh:mm:ss') =date#([Decision Date],'DD-MMM-YYYY hh:mm:ss'), 'Same Time', date#([Decision Date],'DD-MMM-YYYY hh:mm:ss')-Date#([Log Date], 'DD-MMM-YYYY hh:mm:ss') as [Process Time],
Try this:
( Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 as [Process Time] // in Hours
The timestamp will evaluate your string as date+time
The difference between two timestamps will be a difference in days including fractions for hours.
So, if you get 12.5, this means 12 days and half day or 12 days and 12 hours.
I multiplied by 24 to get the result in hours. So, for 12.5 you should get 300 hours.
Hope this helps,
Erich
I can’t seem to reply to threads – javascript: void(0);
Hope this works. Thank you Erich, that works. Is there any way to limit the field to 1 or 2 decimals – it currently shows about 15. So one hour and a few seconds will show as 1.0000000000000164.
Also, I would like to change the process time to show either less than an hour, or 1 – 2 hours, 2 – 3 hours, or more than 3 hours.
Thanks for the help. This is my script currently:
Timestamp(Timestamp#( , 'DD-MMM-YYYY hh:mm:ss')) as ,
Timestamp(Timestamp#( , 'DD-MMM-YYYY hh:mm:ss')) as ,
Timestamp( Timestamp#(, 'DD-MMM-YYYY hh:mm:ss') - Timestamp(Timestamp#( , 'DD-MMM-YYYY hh:mm:ss') )) * 24 as ,
Gerhard Laubscher
Tenacity (a Pepkor company)
P.O. Box 6387, Parow East, 7501, South Africa
T: +27 (0)21 928 1017
F: +27 (0)21 928 1012
C: +27 (0)82 080 4190
www.tenacityinc.co.za<http://www.tenacityinc.co.za/>
gerhardl@tenacityinc.co.za<mailto:stephnik@tenacityinc.co.za
You can adjust the presentation of the field in Settings -> Document Properties -> Number (it's similar to excel)
You could also force it on script level ( you may need to try some different settings) :
num ( ( Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24, '##0.00') as [Process Time]
For the ranges, you could use intervalmatch, but since it's simple, you could just write an if statement:
if(
(Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 <1, 'Less than 1-hour',
if(
(Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 <2, '1-2 hours',
if(
(Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 <3, '2-3 hours','3+ Hours'
)
)
)
Hope I didn't forget any ( or )
Thanks, works beautifully. The forum does not though. I cannot click on reply, nothing happens, I have to reply to the email notification I get. And I cannot find the “Mark as Answer” that used to be there before the site changed.
Gerhard Laubscher
Tenacity (a Pepkor company)
P.O. Box 6387, Parow East, 7501, South Africa
T: +27 (0)21 928 1017
F: +27 (0)21 928 1012
C: +27 (0)82 080 4190
www.tenacityinc.co.za<http://www.tenacityinc.co.za/>
gerhardl@tenacityinc.co.za<mailto:stephnik@tenacityinc.co.za