Discussion board where members can learn more about Qlik Sense App Development and Usage.
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?
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:
Unchecking "Include null values" simply hides them from the graph. That is not what I want. They are a significant portion of the dataset and should not be swept under the rug.
"It is typically best practice not to remove null values from the data model or to turn them into values."
So you think the best practice is to let Qlik wreck your visualizations and filtering capabilities? We'll have to agree to disagree on that one.
lets say you have associated your fact and dimension table on field name Key(Key is the common field in dimension and fact table).
then concatenate a row in your dimension table like
Load 'None' as Key
,'None' as operator autogenrate(1);
Then in your fact table check like if(Isnull(Key),'None',Key) as Key.
Hope this will resolve the problem without joining two tables.
Thanks, but that only helps if the nulls are coming from the fact table itself. In my situation, the nulls arise due to joining. The two tables look like this:
Left Keep (Scrap)
In the database, neither of the tables contain nulls in any relevant columns.
Ok. so you have some [%operatorID] in scrap table which are not there in Operator table.
then do like this-
Load *, [%operatorID] as OperatorIDTemp;
Load [%operatorID],'None' as Operator Resident Scrap where not Exists(OperatorIDTemp,[%operatorID]);
Drop Field OperatorIDTemp From Operator;
Load * Resident Operator; Drop Table Operator;
I may be completely misunderstanding your problem, perhaps sample script or uploading your code would help. From what I can tell you have two tables "Scrap" and "Operators".
ScrapID, ScrapName, OperatorID, OtherScrapDescriptions
OperatorID, OperatorName, OtherOperatorDescriptions
Your table should now have all Scrap Names and all operators associated with each scrap. If a scrap has no OperatorID then it would retain a NULL() for the field OperatorName because there is no matching OperatorID.
Therefore your data model would be 100% correct and Qlik is working as expected. Your table must have scrapIDs without a corresponding OperatorID.
Ah I see now. No it wouldn't make sense to do the manipulation you are thinking of post-load. Just be cognizant of ScrapIDs without OperatorIDs in your data. This is where your null values are being generated.
Dang, I was afraid of that. It seems like an oversight on Qlik's part: extra twiddling at load time being required just to avoid breaking the GUI in the 3 ways I mentioned. 😞