How to deal with nulls in dimensions (not fields)?

[mmarchese]

Qlik handles null values (edit: and missing values) in dimensions in a very frustrating way:

1) You can't click on them, like in the graph below.

2) You can't filter to those values.  Correct me if I'm wrong, but I haven't found a way.

3) They trash your sort order, as shown in the graph below.

4) They trash formatting in tables since they always force a gray background.

Newcomers: skip to edit 3 below.

In the load script, I can do this to replace nulls in fields that will be used as dimensions, making all of the above problems go away.

If(Len(Trim(Operator)), Operator, 'NONE') AS Operator

But that only works if you do all your joining at load time.  If I let Qlik join tables based on key names, then I am powerless to fix them, and my filters and graphs are doomed.  The if-len-trim technique seems to do nothing in dimension formulas.

I've tried this but it seemed to do nothing.  Is this another load-time-only solution?

NullAsValue Operator;
Set NullValue = 'NONE';

Edit1: To be clear, the nulls in question here come from table joins, not from the data in those tables.

Edit2: Added a 4th defect to the list at the top.  The more I think about this, the more confounded I am by Qlik's design decisions.  Why on earth did they go out of their way to program in all of this non-standard, annoying behavior for nulls?

Edit3: I just found this collection of authoritative articles on nulls in Qlik.  I'm settling in to read them all and really hoping that I've just been totally wrong about Qlik being so dang bad at nulls. https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

Edit4: Well, that was very informative reading, and I now know that I'll probably get better help on this forum if I differentiate between "true nulls" and "missing values."  Unfortunately, all 4 of my complaints remain.  Only complaint #2 (filtering to nulls) was addressed whatsoever, and even in that case, the solutions are awkward at best.  These issues have been around for many years, and Qlik's attitude toward them is summarized here as "they're too hard to fix," so I think null/missing value improvements are not on the horizon.

Regarding the original question, it should really be broken down into 4 questions:

• Can you replace true nulls in dimensions?  Yes, using If(IsNull(... or If(Len(Trim(... for text fields or Alt(...) for numeric fields
• Can you replace true nulls in measures?  Yes
• Can you replace missing values in dimensions?  No
• Can you replace missing values in measures?  Yes, if they were due to a cardinality mismatch.  No, if they came about due to no data falling into a particular aggregation bucket (e.g. in pivot table).

My conclusions:

• True nulls should be replaced during loading
• Would-be missing values should be added during loading.  Either OUTER JOIN the tables and do a second pass via RESIDENT LOAD to replace the nulls or keep the tables separate but manually concatenate the missing rows via logic based on EXISTS.

[AndrewHughes]

It isn't extra twidling. I'm still sort of confused by the whole question. I think the data model is either set up wrong or the data is not complete. In either case Qlik is working as intended, unfortunately building charts is only as good as the underlying data.

[mmarchese]

AndrewHughes:

Saying that the data model is wrong is incorrect.  In this case, it is absolutely correct for nulls to be present.  (Edit: to be fair, you are right that this lookup table has been damaged and ought to have all of the operatorIDs present, but it does not have them all and there's nothing I can do to bring them back at this point.  Regardless, it's still true that nulls produced by joining are not always wrong, so the idea is the same.) I don't care whether they are displayed as "Null" or "-" or "None" or whatever.  I just want the GUI not to break because of them.

You admit the 3 problems I listed in my question exist, right?  Can't you see how in the image I included, the sorting is botched because of the nulls?  If you let nulls make it past loading, Qlik is simply not equipped to handle them.  Sorting, filtering, and interaction all stop working.  That is absolutely a huge oversight on Qlik's part.  I was hoping I was wrong about this, but no one has provided any evidence to the contrary.

Replacing nulls at load time is one workaround to this problem.  Yes, I consider it extra twiddling.  Why? Because replacing Null with "Null" just to appease Qlik and prevent its GUI from breaking is not a valuable activity that anyone should spend time on.

I was wondering if dimension formulas could serve as workarounds as well. 

Or whether there was some other method to deal with this.  Some setting or something.

[AndrewHughes]

I was only trying to help. This will be my last comment.

1)

2)

- I don't have an answer for

3) They trash your sort order, as shown in the graph below.

Employee sort by name, or custom sort using expression i.e. MAX(Value)

[mmarchese]

No.  Nulls are always put last, regardless of how you tell it to sort.  Didn't you see the picture I included?  The Operators are clearly sorted according to a numeric expression, and yet the null operator is put at the end.  It's simply wrong.

Yes, communication between us is not working, and we should part ways.  Thanks anyway.

[paolo_mapelli]

IMHO "Qlik People" is splitted into 2 groups: Who understands the problem to have "selectable nulls" (wherever they're coming from...) and the others.

I'm using Qlik Sense since ver. 1.01 and sincerly I really (really) don't have realized the reasons for that. I totally agree on what you wrote in final part of your "Edit4" and that's really upsetting.

Moreover upset when searching for help you have to explain what is obvious for a "SQL person" since the invention of relationship between tables. Sometimes seems that a condition like "<field-of-a-joined-table> is null" is considered as rocket science and  "<field-of-a-joined-table> = <a-value> or <field-of-a-joined-table> is null" as science fiction! And you're constantly strict into questions and lot of "try this", "do that" rather than solutions.

Even wrong model datasets assumptions! Why a null presence in a field should mean this?? Absence of informations is itself an information too!  

Users are getting new, advanced, AI, ML features from Qlik but often still have to struggle on handle nulls as result of star/snowflake schemas joins or other things competitors are able to handle since almost a decade (not just that, of course; another example: sort order in pivot tables...)

Anyway, if you come to a solution please keep in touch. On my side the only and "classic" solution I found to avoid this kind of headaches is to build a huge flat fact table from db and using Qlik Sense only as a presentation layer.

Some will say about performance... Well, I've to give answers and solutions to my customers first.

Regards,