topic New calculated time field in QlikView

New calculated time field

gerhardl — Thu, 21 Apr 2011 12:18:58 GMT

Hi, I have two fields, [Log Date] and [Decision Date], loaded as follows:

Date(Date#( left( [Log Date],9), 'DD-MMM-YY')) as [Log Date],
Date(Date#( [Decision Date], 'DD-MMM-YY')) as [Decision Date],

I would like to create a new field called [Process Time], which subtracts the Log Date from the Decision Date and returns me the number of DAYS.

So if Log Date is 18-APR-2011 and Decision Date is 20-APR-2011 the Process Time must be "2 days".

BUT if the application was logged and finalised on the SAME date (process time = 0 days), it must display as "Same Day"

Thanks,

New calculated time field

chris_johnson — Thu, 21 Apr 2011 12:23:54 GMT

Hi,

Something like:

if([Decision Date]=[Log Date],'Same Day', if([Decision Date]-[LogDate] = 1, '1 day', [Decision Date]-[Log Date] & 'days')) as [Process TIme]

I have not tried this myself though!

Regards,

Chris

New calculated time field

Thu, 21 Apr 2011 12:28:24 GMT

try

if(logdate-decisiondate=0, 'Same', logdate-decisiondate) as process time

New calculated time field

Thu, 21 Apr 2011 12:42:44 GMT

is this working for you? I made little example

t:
LOAD * INLINE [
F1, F2
20-March-2009, 15-March-2009
];

load *, F1 - F2 as diff resident t;

drop table t;

Seams it's not working.

AW:Re: New calculated time field

martinpohl — Thu, 21 Apr 2011 12:49:51 GMT

Hello,

change your example to

load *, date#(F1,'DD-MMMM-YYYY') - date#(F2,'DD-MMMM-YYYY') as diff resident t;

Regards

New calculated time field

gerhardl — Thu, 21 Apr 2011 13:01:05 GMT

It's not working yet, but it's due to our IT department being silly so I havge different date formats, and some are even timestamps and not just dates.

I have

Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],
Date(Date#( [Decision Date], 'DD-MMM-YYYY')) as [Decision Date],

Wil try the other suggestions now...

New calculated time field

gerhardl — Thu, 21 Apr 2011 13:05:30 GMT

Nope...

I now have

Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],
Date(Date#( [Decision Date], 'DD-MMM-YYYY')) as [Decision Date],

if([Log Date]-[Decision Date]=0, 'Same Day', [Log Date]-[Decision Date]) as [Process Time],

It loads fine but process time field is blank

New calculated time field

gerhardl — Thu, 21 Apr 2011 13:09:43 GMT

I now change the expression to the following to try and track down the problem:

if([Log Date]-[Decision Date]=0, 'Same Day', 'No') as [Process Time],

And they are all now "No". So this means that the two fields (even though the dates are the same for most) are not seen as the same. This must be due to the fact that one is a time stamp ('DD-MMM-YYYY HH:MM:SS') and the other just a date ('DD-MMM-YYYY').

New calculated time field

Thu, 21 Apr 2011 13:10:11 GMT

try this


if(date#([Log Date],'DD-MMMM-YYYY') =date#([Decision Date],'DD-MMMM-YYYY'), 'Same Day', date#([Log Date],'DD-MMMM-YYYY') -date#([Decision Date],'DD-MMMM-YYYY')) as [Process Time],

New calculated time field

gerhardl — Thu, 21 Apr 2011 13:29:18 GMT

This is driving me crazy.

I tried the above and got an error (remember it's MMM not MMMM). I played around with it a bit, but there are too many damn ('s and )'s I can't get it right!

At the moment I have

if(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) =date#([Decision Date],'DD-MMM-YYYY'), 'Same Day', Date#( left( [Log Date],11)), 'DD-MMM-YYYY' -date#([Decision Date],'DD-MMM-YYYY') as [Process Time],

But the error tells me "if takes 2-3 parameters"

New calculated time field

chris_johnson — Thu, 21 Apr 2011 13:32:10 GMT

Think you have an extra bracket in at the start

if( Date#( left([LogDate],11),'DD-MMM-YYYY') = date#...

New calculated time field

gerhardl — Thu, 21 Apr 2011 13:36:05 GMT

Finally Success!!!

if(Date#( left( [Log Date],11), 'DD-MMM-YYYY') =date#([Decision Date],'DD-MMM-YYYY'), 'Same Day', date#([Decision Date],'DD-MMM-YYYY')-Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Process Time],

Thank you guys,

New calculated time field

chris_johnson — Thu, 21 Apr 2011 13:42:41 GMT

Glad you got there in the end!

New calculated time field

Thu, 21 Apr 2011 13:50:57 GMT

Glad you solved your problem.

actually thank other guys who posted here. I'm just mixed their script ^^