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: 
Anonymous
Not applicable

Remove Hyphen in Bar Chart for Null Values

Hi,

I have a bar chart which is supposed to show null values count as well and it gets displayed correctly. The problem is that the label for the null values is Hyphen "-".

How can I replace the hyphen with a custom text, say "Unassigned"?

Capture.PNG

Thanks,

Sandeep

17 Replies
sunny_talwar

Can you share a sample?

Anonymous
Not applicable
Author

if it doesn't work, then in your script, while loading the field Sector, do this:

If(len(Sector)>0,Sector,'Unassigned') as Sector

Anonymous
Not applicable
Author

It gives the error message: "Garbage after expression: as" in the edit expression pop up window.

Is that what you mean by "in your script"?

NZFei
Partner - Specialist
Partner - Specialist

Hi Sandeep,

You will have this problem again and again in the future so you need to know how to handle it.

Please see the script in attached the sample file for details.

Hope it can help.

Fei

ArnadoSandoval
Specialist II
Specialist II

Hi Sandeep

You may try these variables setting in your load script (Qlik Sense june-2017 version, I know it work in previous version); Our scenario was with dashes appearing when exporting to Excel, perhaps it may resolve your issue.

NULLASVALUE *;

SET NullValue='';

HTH

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Anonymous
Not applicable
Author

Hi Fei,

Thanks a lot for taking the time to put together the sample qvf. Now, I understand where to write the scripts and how to create sample qvfs.

Now, I can see a category/classification called "Undefined" with count 0, and I still see a category with '-' and count 5. Below is the script that I wrote.

Fact:

load

industrycodekey_ as SectorID

Resident dimentity;

Sector:

load

industrycodekey_ as SectorID,

industrycodekey_ as SectorIDInSectorTable // Notice  here we create a new field.

Resident dimindustry;

//dimentity and dimindustry are the names of my tables with industrycodekey_ as the foreign key.

Concatenate (dimindustry)

load Distinct

SectorID,

'Unassigned' as Classification,

'Unassigned' as Sector,

'Unassigned' as PeerID

Resident Fact

where not Exists(SectorIDInSectorTable, SectorID);

drop field SectorIDInSectorTable;

I looked at the data in the dimentity table and the issue is not that the dimentity has a value not present in dimindustry, it because dimentity has NULL value for the industrycodekey_. How should I change the script now?

Capture.PNG

Thanks,

Sandeep

simon_minifie
Partner - Creator III
Partner - Creator III

Hi Sandeep,

Second what Arnaldo stated above:

Set NullValue = 'Unassigned';

NullAsValue <Field Name>;

Thanks,

Simon

ArnadoSandoval
Specialist II
Specialist II

Sandeep,

This link takes you to the Qlik Sense documentation for the value handling suggested to you:

NullAsValue

NullValue

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.