where =< 2017-01-01 and to include nulls?

[#1]

I need a where clause that in the load I filter out a date but still retain the null data.

[sunny_talwar]

May be like this

LOAD *

FROM ...

Where If(Len(Trim(DateField)) = 0, MakeDate(2017, 1, 1), DateField) <= MakeDate(2017, 1, 1);

[swuehl]

There are two things to consider

a) you want to combine different conditions in a WHERE clause

You can use logical operators to achieve this:

Logical operators ‒ QlikView

The OR operator would be useful in your case

b) you want to check for NULL

There are two common approaches to check for NULL, IsNull(FIELD)  checks for NULL and returns true if FIELD is NULL, Len(Trim(FIELD)) returns zero for NULL or if the FIELD value is empty or blank.

In total

LOAD

     DateField,

     ...

FROM ....

WHERE DateField <= '2017-01-01' OR Len(Trim(DateField));

[#4]

Or can I just auto populate the nulls with a date of 2099-01-01?

Or can a us a measure to exclude all dates of 2016-12-31 and keep the nulls?

[sunny_talwar]

Seems like you performing this in the SQL SELECT Statement instead of load statement... would you be able to share the SELECT Statement you have?

[#6]

I get an error

FROM "Pat"."dbo"."Patients"

Where If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1);

Or can I just auto populate the nulls with a date of 2099-01-01?

[sunny_talwar]

Try this

SQL SELECT *,

     NVL(DischargeDt, TO_DATE('01JAN2099', 'DDMONYYYY')) as DateField

FROM "Pat"."dbo"."Patients"

Where NVL(DischargeDt, TO_DATE('01JAN2017', 'DDMONYYYY')) <= TO_DATE('01JAN2017', 'DDMONYYYY');

[#8]

[Patients]:
LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';
// LIB CONNECT TO 'pharmoresql (pharmore_dewing)';

LOAD
  [FacID] as PFacID,
[PatID] as PPatID,
[PatLName],
[PatFName],
[PatStatus],
    [AdmDate],
//    01/01/0001 as Blankdate,
     DischargeDt,
// isNull(DischargeDt, 00/00/0000),
[DefaultExempt340B];
  
SQL SELECT  "FacID",
  "PatID",
  "PatLName",
  "PatFName",
"PatStatus",
"AdmDate",
  "DischargeDt",
  "DefaultExempt340B"
FROM "Pat"."dbo"."Patients"

Where If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1);

[#9]

yes it was in the select but this is what happens in load

//    01/01/0001 as Blankdate,

//      DischargeDt,

     If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1),

[#10]