Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"?
Thanks,
Sandeep
Can you share a sample?
if it doesn't work, then in your script, while loading the field Sector, do this:
If(len(Sector)>0,Sector,'Unassigned') as Sector
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"?
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
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
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?
Thanks,
Sandeep
Hi Sandeep,
Second what Arnaldo stated above:
Set NullValue = 'Unassigned';
NullAsValue <Field Name>;
Thanks,
Simon
Sandeep,
This link takes you to the Qlik Sense documentation for the value handling suggested to you:
Hope this helps,