Filtering and/or excluding nulls

flytrapper — Thu, 10 May 2018 17:09:14 GMT

I have read many posts here regarding the handling of nulls in Qlik Sense, and have yet to find a satisfactory solution. The problem, as I see it, is this:

If we have a field that could potentially contain a null value, there may be some instances where we want to be able to filter those nulls either by selecting null values in a filter pane (which is not possible) or click on a null value in a table (also not possible). The solutions are to either (a) use one of Qlik's NullHandling functions to convert nulls to some non-null value during the load, (b) convert the nulls in the load script to a non-null value via an "if" statement, or (c) convert the nulls in a chart's expression editor to a non-null value via an "if" statement.

All of these options then make the field in question filterable and searchable, but then the very handy and oft-used "Include null values" checkbox for a given dimension is rendered useless for any filed that has been converted from a null to a non-null value (option c above also has the added disadvantage of displaying a less-than-aesthetically-pleasing expression in the Selections bar, as opposed to the simple-and-clean field name).

The only option I can see is to create a second field for each and every field that we might want to serve these dual purposes: the original still-has-nulls field would be selected in charts when we want to uncheck the "Include null values" box for a dimension, and the modified copy of the original field would be selected in charts when we want nulls to be filterable/searchable.

There has to be a better way.

Ideally, the better way would be for Qlik to make nulls searchable/filterable by default, but until that happens, does anyone have a clean solution for dealing with this?

Thanks in advance for any insight shared!

--Mike

topic Filtering and/or excluding nulls in App Development

Filtering and/or excluding nulls