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]

If you do not want your visualizations to display null values in dimensions simply select your dimension to open up the drop down. Then deselect "Include null values". It is typically best practice not to remove null values from the data model or to turn them into values.

[mmarchese]

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.

[sagarjagga]

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

Concatenate(dimension table)

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.

[mmarchese]

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:

Scrap:
SELECT
    [%operatorID],
    ...
FROM Scrap;

Operator:
Left Keep (Scrap)
SELECT
    [%operatorID],
    Operator
FROM Operator;

In the database, neither of the tables contain nulls in any relevant columns.

[sagarjagga]

Ok. so you have some  [%operatorID]  in scrap table which are not there in Operator table.

then do like this-

Scrap:
SELECT
    [%operatorID],
    ...
FROM Scrap;

Operator:

Load *,  [%operatorID] as OperatorIDTemp;
SELECT
    [%operatorID],  

Operator

FROM Operator;

Concatenate(Operator)

Load  [%operatorID],'None' as Operator Resident Scrap where not Exists(OperatorIDTemp,[%operatorID]);

Drop Field OperatorIDTemp From Operator;

NoConcatenate Operator1:

Left Keep(Scrap)

Load * Resident Operator; Drop Table Operator;

[AndrewHughes]

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".

Scrap:

ScrapID, ScrapName, OperatorID, OtherScrapDescriptions

;

LEFT JOIN(Scrap)

Operator:

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.

[mmarchese]

Sagarjagga:

Thanks, that's interesting.  I might make use of that.

Still, I was hoping for a post-load solution.

[AndrewHughes]

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. 

[mmarchese]

AndrewHughes:

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.  😞