Expression for replacing the label for a count of Null values

[#1]

I have a pie chart object that counts the number of statuses based on the data below. There are "null" values and in Qlik, it label's it "-". How can I update the label of null counts to a different label, like "Other"?

Total Count
	35
Green	13
Yellow	1
Red	2
-	19

I would like to know how to write the expression for the replacement of the "-" to "Other" and where to put it via the UI (via chart properties) as well as how to write the expression to add to the script.

Thanks!

Trista

[Digvijay_Singh · Accepted solution]

In presentation tab, you can change symbol of Null.

[Digvijay_Singh]

In the script If you want to give specific name to Null values you are loading from your source, you should use this before the load -

NullAsValue *;    //* means all the fields, you can use it for specific fields as well by writing field names here.

Set NullValue = 'IamNull';

This won't work for the nulls generated through joins during runtime while visualizing charts, for those kinda null you need to use Null symbol available in Presentation tab.

[rubenmarin]

NullValue also admits to pick the fields if you don't want to load all nulls as value:

Set NullValue = 'Other';

NullAsValue ColorField;

There is also the option to check while loading the table:

LOAD If(IsNull(ColorField), 'Other', ColorField) as ColorField,

...

[shree909]

U can write an if condition to replace the null value with others If(len(fieldname)=0,'Others',Fieldname)  as a calculated Dimension Hope this helps. If not please share a sample with the expected output. Thanks

[zebhashmi]

can you write it with load example?

[Digvijay_Singh]

Like this -

Set Nullinterpret = '%';     //In real scenario it won't be needed, here I am forcing '%' symbol to be interpreted as null;

NullasValue Dim;

Set NullValue = 'IamNull';

Dim:

LOAD * Inline [

Dim

1

2

%

];

[#8]

Thanks Digvijay!

Changing the Null Value via the Presentation did change the value in Qlik, but now if I copy this table from Qlik into Excel (Copy to Clipboard > Full Table), the "Other" label does not copy/paste. Instead, it's just <blank>. I'm creating a macro to copy over this table to Excel. Is there another way to ensure I don't lose this label? Or is it better to just script it?

Total Count
	35
Green	13
Yellow	1
Red	2
Other	19

[Digvijay_Singh]

Not sure about export behavior but I prefer scripting it for specific field. But ensure its not causing any impact on null related codes and expressions.