MagicETL2 Filter issue?

mberkeley βšͺ️
edited March 23 in Dataflows

I have a filter in my dataflow:

id NOT EQUAL value

This filter removes all rows that have 'value', but it also seems to remove rows that have null in the 'id' field. Has anyone seen similar behavior in the Filter Rows ETL?

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ πŸ”΅
    Accepted Answer

    If you are wanting to keep the NULL values, you may want to try adding two options to your filter card. NULL values do not play nicely when checking if they are equal to anything.

    In addition to the id NOT EQUAL value, try including

    id IS NULL

    Then set the filter rules to look for data that meet ANY rule.


  • mberkeley
    mberkeley βšͺ️

    Thanks. Trying this now.

  • mberkeley
    mberkeley βšͺ️

    The answer above worked. Thank you.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    well... ok. to put more nuance on the answer

    if you read NULL as "I don't know" and you imagine that in your hand you have the value NULL. If you asked "does my hand NOT contain 7" you assume the answer is FALSE. because you don't know what's in your hand. it works the same way if you say "does my hand = 7" similarly the answer must be FALSE because you don't know what's in your hand.

    The only thing you know for sure, is that your hand IS NULL. (or IS NOT NULL) but if you test an explicit value you will always get FALSE

  • mberkeley
    mberkeley βšͺ️

    BTW. My original version of this works in the original ETL tools, just not in ETL2.

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    @mberkeley That is correct. There was a fundamental shift with ETL 2.0 to more align with SQL handling of NULLs which give consistency but can cause issues in dataflows when converting from ETL 1.0 -> ETL 2.0