Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master III
Master III

In presentation tab, you can change symbol of Null.

View solution in original post

8 Replies
Highlighted
Master III
Master III

In presentation tab, you can change symbol of Null.

View solution in original post

Highlighted
Master III
Master III

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.

Highlighted

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,

...

Highlighted
Partner
Partner

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

Highlighted
Specialist
Specialist

can you write it with load example?

Highlighted
Master III
Master III

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

Highlighted
Not applicable

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
Highlighted
Master III
Master III

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.