Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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

1 Solution

Accepted Solutions

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

You want Jan 2017 and forward and you don't want to bring in nulls? May be this

DischargeDt as DateField

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

Where COALESCE(DischargeDt, CONVERT(DATETIME, '2000-01-01')) >= CONVERT(DATETIME, '2017-01-01');

This will now bring everything 01/01/2017 and forward (just as you asked)

I  only want to pull in DischargeDt of 01/01/2017 and forward.

and won't bring the nulls....

16 Replies

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

May be like this

LOAD *

FROM ...

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

MVP
MVP

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

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

Not applicable

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

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?

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

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?

Not applicable

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

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?

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

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

Not applicable

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

[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);

Not applicable

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

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

Not applicable

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

Community Browser