Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In presentation tab, you can change symbol of Null.
In presentation tab, you can change symbol of Null.
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.
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,
...
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
can you write it with load example?
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
%
];
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 |
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.