Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression for replacing the label for a count of Null values

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
Green13
Yellow1
Red2
-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

1 Solution

Accepted Solutions
Digvijay_Singh

In presentation tab, you can change symbol of Null.

View solution in original post

8 Replies
Digvijay_Singh

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
Partner - Specialist II
Partner - Specialist II

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

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

%

];

Capture.PNG

Not applicable
Author

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
Green13
Yellow1
Red2
Other19
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.