Filtering data will completely remove blank values

Christianjmzc

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?

a10hall

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

@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

Christianjmzc

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!