Filter is removing nulls plus specified value

NateBI · 2023-02-03T17:12:41+00:00

There was an error rendering this rich post.

NateBI

Hi All,

Question:

I am using a Magic ETL filter with:

level = 2
status = 'Blue'

The filter is removing 'Null' values in the status field as well as the Blues. What's the potential cause here?

Context:

Both fields are string dtypes (abc)

I've tried:

level = 2 AND status <> blue -- in one formula
status NOT IN('Blue')
Creating another filter transformation -- same issue

All responses appreciated

Kind regards,

Find more posts tagged with

Accepted answers

All comments

RobSomers

@NateBI Nulls don't play well with logical operators. Null isn't the same thing as nothing, it really means that the value is unknown. Is null blue? SQL doesn't know so it treats it as if it meets the condition.

https://en.wikipedia.org/wiki/Null_%28SQL%29

You can use a formula rule to get around this:

IFNULL('status','') = 'Blue'

This will replace your NULL value with a blank which it can evaluate in the comparison.

Alternatively, you could use the formula:

'status' = 'Blue' or 'status' IS NULL

NateBI

Hi @RobSomers thank you for this -- this concept worked I just adapted it to filter out the Blues but keep NULLs with :

IFNULL(`status`<>'Blue',TRUE)

Thanks!