Filtering data will completely remove blank values

Christianjmzc
Christianjmzc Member
in Magic ETL

Hi community,

I'm not sure if this is by design or if this is a bug.

Filtering data within a field will completely filter out the blank values as well. It looks like Domo won't recognize the Blanks as a value.

This is an scenario:

FieldA, FieldB

CategoryA,12345

CategoryB,58679

,89658

CategoryD,97865

CategoryY,74935


Filter: FieldA not equal to 'CategoryA'


The output will also remove the blank for FieldA:

FieldA, FieldB

CategoryB,58679

CategoryD,97865

CategoryY,74935


I've noticed this issue while filtering a Pivot table so I decided to apply the filter within the Magic ETL. Both had the same result!

This sould not be happening.

Any ideas?

Tagged:

Comments

  • a10hall
    a10hall Member

    This is how DOMO has handled filtering with null values for awhile now; I can't remember if it was for New ETL or an update after that. I suggest assigning a value for the NULL values using Value Map or Formula in ETL.

    CASE WHEN FieldA IS NULL THEN 'Blank' ELSE FieldA END

    This should save the data from being deleted by your filter.

  • RobSomers
    RobSomers Coach

    @Christianjmzc @a10hall This is actually normal behavior for SQL. If you tried it in SQL server or MySQL, you would get the same result because in SQL, nulls don't work with logical operators as you'd expect. This Wikipedia entry explains it pretty well:

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


    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • Christianjmzc
    Christianjmzc Member

    Thank you @a10hall, this path is what we ended up following. Although it is a headache when you have hundreds of datasets with hundreds of ETL's.

    @RobSomers you are right, it is a normal behavior for SQL. However, I would expect that DOMO would handle this like other BI Tools or at least have documentation, even a small note while creating filters.

    Thank you both!

Categories