topic Re: How to deal with nulls in dimensions (not fields)? in App Development

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

mmarchese — Mon, 20 Dec 2021 21:32:42 GMT

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.

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

AndrewHughes — Thu, 27 Feb 2020 17:30:39 GMT

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.

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

mmarchese — Thu, 27 Feb 2020 17:39:51 GMT

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.

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

sagarjagga — Thu, 27 Feb 2020 17:41:01 GMT

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.

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

mmarchese — Thu, 27 Feb 2020 18:06:27 GMT

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.

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

sagarjagga — Thu, 27 Feb 2020 18:26:22 GMT

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;

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

AndrewHughes — Thu, 27 Feb 2020 19:51:26 GMT

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.

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

mmarchese — Thu, 27 Feb 2020 20:41:24 GMT

Sagarjagga:

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

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

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

AndrewHughes — Thu, 27 Feb 2020 20:28:59 GMT

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.

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

mmarchese — Thu, 27 Feb 2020 20:46:16 GMT

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

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

AndrewHughes — Thu, 27 Feb 2020 21:04:59 GMT

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.

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

mmarchese — Thu, 27 Feb 2020 21:55:47 GMT

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.

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

AndrewHughes — Fri, 28 Feb 2020 15:49:44 GMT

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

- 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)

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

mmarchese — Fri, 28 Feb 2020 16:12:00 GMT

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.

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

paolo_mapelli — Fri, 28 Aug 2020 14:05:06 GMT

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,