New calculated time field

[gerhardl]

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,

G

[chris_johnson]

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

[#3]

try

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

[#4]

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.

[martinpohl]

Hello,

change your example to

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

Regards

[gerhardl]

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...

[gerhardl]

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

[gerhardl]

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

?

[#9 · Accepted solution]

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

[gerhardl]

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"