topic Re: Comparing two dates and two times where nulls exist in App Development

Comparing two dates and two times where nulls exist

acholchemit — Sat, 16 Nov 2024 19:55:25 GMT

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?

Re: Comparing two dates and two times where nulls exist

jerifortune — Thu, 10 Oct 2019 17:54:21 GMT

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.

Re: Comparing two dates and two times where nulls exist

sunny_talwar — Thu, 10 Oct 2019 18:22:18 GMT

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

Re: Comparing two dates and two times where nulls exist

acholchemit — Fri, 11 Oct 2019 09:19:14 GMT

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.

Re: Comparing two dates and two times where nulls exist

acholchemit — Fri, 11 Oct 2019 11:07:40 GMT

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