Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

Comparing two dates and two times where nulls exist

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.

clipboard_image_0.png

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

Labels (5)
1 Solution

Accepted Solutions
Highlighted

Re: Comparing two dates and two times where nulls exist

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

View solution in original post

4 Replies
Highlighted
Contributor III

Re: Comparing two dates and two times where nulls exist

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.

Highlighted

Re: Comparing two dates and two times where nulls exist

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

View solution in original post

Highlighted
New Contributor II

Re: Comparing two dates and two times where nulls exist

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.

 

Highlighted
New Contributor II

Re: Comparing two dates and two times where nulls exist

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