Null Values in Qlik Cloud Best Practices

[rc-infra]

Hello

Some of my reporting has many null values for metric, string, and date fields. while i know that data type isn't exactly a thing in qlik (blows my mind), but what is best way to handle nulls to do reports on these? I cannot filter by nulls, not can i select rows with nulls. One of my analysts told me qlik told him to mask the fields as 0's for metric and a string that says "Null" for strings. this seems incorrect as well as adding 0's to a data distribution could provide false analysis.

thanks

[rubenmarin]

Hi, I agree that metrics shouldn't be changed, as this might change aggregated values, if really needed I would use another field to filter metrics without values.

[rc-infra]

thanks for reply - what do you mean use another field to filter metrics without values? how would you set this up?

[rc-infra]

anyone have thoughts on this? or is this just a weakness of qlik?

[hic]

Qlik handles NULLs correctly as I see it. 

I don't understand what you want to do:

• Do you want to remove records with NULLs?
• Do you want to keep them as NULLs?
• Do you want to convert them to something else (e.g. empty string) and make them selectable?

See also the following blog posts. They are about QlikView, but the NULL handling works in exactly the same way in Qlik Sense.
https://community.qlik.com/t5/Design/NULL-The-Invisible-Nothing/ba-p/1467674
https://community.qlik.com/t5/Design/Finding-NULL/ba-p/1474279

[rubenmarin]

Hi, you can just create another field to flag the records with null in one or more fields, like:

If(IsNull(FieldtoCheck) or IsNull(AnotherFieldToCheck),'With Nulls','All Value') as NullRecords

[rc-infra]

i want to be able to drill down in a report to view nulls, perform group by summaries on say things "count by: item 1, item 2, and nulls". 

it appears that the only way to do this is add another parameter to the data set (so if you have 80 fields, you double your fields) and you have to perform group by analysis of nulls separately from group by analysis of the fields they appear in?

[rc-infra]

thx for response - see my reply to hic above. it sounds like this prevents a simple group by analysis in the field the null truly exists in as well as doubles the fields in report for as many fields might contain nulls

[hic]

You have two cases: Either the field is used as dimension or it is used inside a measure.

Dimensions:
If you use the field as it is, NULLs can be used as "Group By" symbol. Just make sure that you don't hide NULLs in the chart. But you cannot select the NULL. To do this, you need to create a second field, e.g.
Coalesce(Field,'NULL') as NewField

But you don't need to do this for all fields - you only need it for fields where you have NULLs and you want to be able to select them.

Measures:
Here you don't need to do anything: NULLs are treated correctly. For example, NULL will not affect Sum() or Count().

[rc-infra]

thanks for your reply @hic .

Dimensions:

I see, I will try the mentioned group by method. To not be able to select nulls I understand this is the case, but is there any sense behind this? If you are trying to look at a subset of data for comparative analysis on a Dimension, selecting nulls and/or other values in a parameter seems to be common practice. If a data set is >100+ dimensions, adding a column for every case seems inefficient, though i would understand if there is a legit reason for this?

Measures:

Understood, is there a Count that includes Nulls in order to count all metric records in a data set for a parameter - nulls included?