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: 
Marco_Calvo
Contributor
Contributor

Field in Blank not showing the Chart legend

Hi,

I'm loading in data from a .xls file.

One of the charts I'm using is based on a field with 2 categories (A,B), but some entries don't apply to any of these 2 categories, they just have a blank space.

The pie chart is correct, showing all values divided in three sections, but in the legend I get only categories A and B. I would like to show in the legend another category "Undefined" or "TBD" for entries with blank space.

Is it possible to define that all entries not falling on categories "A" or "B" are shown under category "Undefined"?

Thank you!

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

Hi, it's returning and error because [Status part] is loaded 2 times, remove the firsts one.

LOAD...

PMT,

[Part Status], // delete or comment this one

[Send Email?]

View solution in original post

10 Replies
rubenmarin

Hi, if you want to select those values you can create the desired values in script, in example checking if Category has a value, if not assign 'Undefined':

LOAD

  If(Len(Trim(Category))=0, 'Undefined', Category) as Category,

 ...

Marco_Calvo
Contributor
Contributor
Author

Thank you, but I've tried this solution and it's still not working.

I understand the main idea is to QlikView assign "Undefined" for all values in blank, but it's not working.

Any other tip?

P.S.: Field is [Status] and values are "New", "Current" and of course there are cells in blank.

 

rubenmarin

Have you tried using Status instead of Category?

LOAD

  If(Len(Trim(Status))=0, 'Undefined', Status) as Status,

 

It will look, if length of string removing spaces is equal to zero it stores 'Undefined', else it takes original Status value.

Marco_Calvo
Contributor
Contributor
Author

Yes, I tried that, but when I click on the "Reload" I get the following error message:

clipboard_image_0.png

 

rubenmarin

Hi, it's returning and error because [Status part] is loaded 2 times, remove the firsts one.

LOAD...

PMT,

[Part Status], // delete or comment this one

[Send Email?]

sugathirajkumar
Creator
Creator

A:
load * inline [
category ,sales,Id,
table,100,1
chairt,200,2
table,200,1
chairt,400,2
table,500,1
chairt,200,2
table,250,1
chairt,350,2
,,3
,,3
,,3
];

Res:
load * ,
if(category='','unknown',category) as CAlDim;
load
category,sales,Id
Resident A;
drop table A;

exit script;

clipboard_image_0.png

 

sugathirajkumar
Creator
Creator

A:
load * inline [
category ,sales,Id,
table,100,1
chairt,200,2
table,200,1
chairt,400,2
table,500,1
chairt,200,2
table,250,1
chairt,350,2
rod,200,3
rod,310,3
rod,230,3
];

Res:
load * ,

if(match(category,'table','chairt'),category,'unknown') as CAlDim ;

load
category,sales,Id
Resident A;
drop table A;

exit script;

 

sugathirajkumar
Creator
Creator

if(match([Status],'New','Current'),[Status],'unknown') as CAlDim ;

Marco_Calvo
Contributor
Contributor
Author

Thank you! It worked just fine.