Comparing two dates and two times where nulls exist

[acholchemit]

Hi,

I have a problem where I need to compare a date field and time field with another date field and time field.  The issue is that the data is dirty and so 3 of the four fields could contain nulls. 

I need to do this comparison in order to determine if a delivery is late or on time (including early). 

As there are nulls my theory was to 'force' a date or time into the field where there are nulls in order to allow the comparison to work.  Unfortunately this doesn't seem to work despite the output looking valid.

The relevant script is as follows;

Load *,

//Doing the comparison

IF([PurchaseRcptLine ReceiptDT] > [PurchaseRcptLine PromisedDT], 'Late', 'On Time') AS [PurchaseRcptLine Late/On Time2];

Load *,

//Firstly building the two fields to compare

Timestamp#([PurchaseRcptLine Posting Date] & ' ' & IF(ISNull([PurchaseRcptLine Actual Receipt Time]),Time#('00:01'),[PurchaseRcptLine Actual Receipt Time]),'DD/MM/YYYY hh:mm') AS [PurchaseRcptLine ReceiptDT],

Timestamp#(IF(ISNULL([PurchaseRcptLine Promised Receipt Date]),'31/12/2019',[PurchaseRcptLine Promised Receipt Date])
& ' ' & IF(ISNULL([PurchaseRcptLine Requested Receipt Time]),Time#('23:59'),[PurchaseRcptLine Requested Receipt Time]),'DD/MM/YYYY hh:mm') AS [PurchaseRcptLine PromisedDT];

The output ends up like this with the flag showing as Late when it should be On Time.

Can anyone shed any light on what is happening\ what I am doing wrong?

[jerifortune]

It would be nice to have a sample data. I need to understand the nature of the dirty data to be able to apply the correct transformation.

[sunny_talwar · Accepted solution]

May be try this

LOAD *,

//Firstly building the two fields to compare

TimeStamp#([PurchaseRcptLine Posting Date] & ' ' & If(IsNull([PurchaseRcptLine Actual Receipt Time]), Time#('00:01'), Time([PurchaseRcptLine Actual Receipt Time], 'hh:mm')), 'DD/MM/YYYY hh:mm') as [PurchaseRcptLine ReceiptDT],

TimeStamp#(If(IsNull([PurchaseRcptLine Promised Receipt Date]), '31/12/2019', [PurchaseRcptLine Promised Receipt Date]) & ' ' & If(IsNull([PurchaseRcptLine Requested Receipt Time]), Time#('23:59'), Time([PurchaseRcptLine Requested Receipt Time], 'hh:mm')), 'DD/MM/YYYY hh:mm') as [PurchaseRcptLine PromisedDT];

[acholchemit]

Hi Sunny,

This appears to have worked.  I am checking through some of the other use cases but the specific example I screenshotted has flipped to be On Time as it should be.

Thanks for the assistance, it's much appreciated, I'll check back again when I have done some further analysis.

[acholchemit]

Just to confirm, Sunny's solution has worked and so i will mark it as the answer.