Why is a field not allowing me to exclude it?

nshively
nshively Member
edited March 2023 in Datasets

I recently built a new field for my dataset in Excel that added certain employees into specific groups. Most employees are not in these groups, so when I joined that field to the rest of my dataset most of the fields are blank. This should be fine.

However, when I add this field as a filter into any dashboards or charts, it does not allow me to filter by ‘blanks’ or exclude all of the groups. If I do this, no data is returned.

The field is incredibly basic and laid out like:

“Employee Group:
Group 1
Group 2
Group 3”

If an employee is not part of a group, a blank field is created as it's joined. I would like to be able to filter by employees not a in a group.

I have plenty of other columns with blank fields, such as ‘Exit Date’. If an employee has not left the company, ‘Exit Date’ is blank and I have no problem sorting by employees with a blank exit date. Therefore, this problem makes absolutely no sense to me.

Additionally, if I do an ‘ifnull’ formula, it overwrites everything including fields with values.

Tagged:

Answers

  • RobSomers
    RobSomers Coach

    @nshively When you you join your Groups to your original dataset, if an Employee from your original dataset is not in your Groups dataset, then Domo (SQL) will make it a NULL. So your values are actually NULL not blank, and filtering with NULLS doesn't really work unless you specifially filter on IS NULL or IS NOT NULL. This wikipedia entry has a good explanation of why this is:

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

    Now it is interesting that your IFNULL formula isn't working because that would be the solution to make it so they are not null. Would you mind posting the beast mode you're using for that? Also, since IFNULL is giving you trouble, you could use a beast mode like this:

    case when ‘Group’ IS NULL then ‘Other’ else ‘Group’ end

    And then just filter out Other.

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

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

Categories