Magic ETL: How to REMOVE Rows Meeting a Two-Column Criteria?

pstrauss

As part of my ETL, I'm trying to REMOVE rows that meet the following criteria:

column 1 is NOT NULL

AND

column 2 is NULL

So if column 1 has any value in it AND column 2 is empty, then remove those rows from the data set.

I was trying to figure out how to create a Filter Formula for this, but it seems to be designed for INCLUDING rows that meet criteria rather than REMOVING them.

GrantSmith

Think of a filter tile as trying to selected the rows that match a certain criteria. If you're using a filter formula you can invert the logic. So if you know the logic you want to remove add a NOT in front of everything encapsulated in parenthesis.

NOT (`column 1` IS NOT NULL AND `column 2` IS NULL)

If you take the inverse of that logic you'd take out your NOT NULL and replace with NULL and replace NULL with NOT NULL and also convert AND conditions to OR condition and OR conditions to AND conditions.

RobSomers

@pstrauss You would just do the opposite of what you're wanting to remove:

column 1 is null and column 2 is not null

GrantSmith

Think of a filter tile as trying to selected the rows that match a certain criteria. If you're using a filter formula you can invert the logic. So if you know the logic you want to remove add a NOT in front of everything encapsulated in parenthesis.

NOT (`column 1` IS NOT NULL AND `column 2` IS NULL)

If you take the inverse of that logic you'd take out your NOT NULL and replace with NULL and replace NULL with NOT NULL and also convert AND conditions to OR condition and OR conditions to AND conditions.

pstrauss

Thanks @GrantSmith and @RobSomers that's very helpful!

pstrauss

On a related note, what's the most reliable way to filter out rows with specific values in them but not null rows?

For example, I want to remove rows where column_1="A" OR column_1="B" but not where column 1 is NULL?

I originally tried two filters with an ALL setting where column_1<>"A" or column_1<>"B" but it seems to remove NULLs too when I do that.

I think it's the inverted logic that is throwing me off.

RobSomers

@pstrauss Nulls don't play well logical operators. Null doesn't mean there's no value, it means the value is unknown. Is the null in column_1 equal to A? SQL doesn't know. It might or it might not, so SQL treats at as meeting the condition. That's a simplified answer, but this gives a more in depth explanation:

Null (SQL) - Wikipedia

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

In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information".

So you will either need to include IS NOT NULL or IS NULL in your filter rules or formula to account for NULLS or use IFNULL() in your filter to assign a value to the nulls that can be used in the comparison.