Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

16 Replies
sunny_talwar

I guess sql server doesn't have NVL... try with COALESCE

SQL SELECT *,

    COALESCE(DischargeDt, CONVERT(DATETIME, '2099-01-01')) as DateField

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

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

Not applicable
Author

      COALESCE(DischargeDt, CONVERT(DATETIME, '2099-01-01')) as DateField

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

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

I still pick up all records but it shows a date of 1/1/2099 where they were null.

swuehl
MVP
MVP

Robert, could you elaborate on the DischargeDt values that still pass the WHERE clause and that you want to exclude?

The WHERE clause will let all values pass where the WHERE condition returns TRUE.

Your current condition returns TRUE for all values before and including Jan 1st, 2017.

In a previous post, you said

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

which is contradicting the current condition. Maybe you just need to adjust the date limit and / or comparison operator in your WHERE clause.

sunny_talwar

In your initial post you mentioned

"filter out a date but still retain the null data"


So, it seemed you do want to keep the null value in the dates. Then you mentioned this


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

I tried to provide you with a code which changes the null value to the date you asked for... now I am confused as to what exactly you are looking to get? Is this not what you wanted

Not applicable
Author

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

But what I am really trying to accomplish is a way to count number of patients we have that are active by month.

starting for the month of January 2017.

A trending report by month of patients that were active in each month.

Data in report

!

swuehl
MVP
MVP

So you want to filter your dates more like

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

For your other request (which is shifting / extending the topic of your thread quite a bit),have a look at

Creating Reference Dates for Intervals

sunny_talwar

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....