topic Re: Date format problem in QlikView

Date format problem

gerhardl — Mon, 30 May 2011 14:21:12 GMT

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

Date format problem

Mon, 30 May 2011 14:48:00 GMT

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],

Date format problem

erichshiino — Mon, 30 May 2011 14:49:24 GMT

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

Re: Date format problem

gerhardl — Mon, 30 May 2011 14:56:56 GMT

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

Re: Date format problem

erichshiino — Mon, 30 May 2011 15:11:16 GMT

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 )

Re: Date format problem

gerhardl — Tue, 31 May 2011 07:57:39 GMT

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