Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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,

 ...

Contributor
Contributor

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.

 

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.

Contributor
Contributor

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

clipboard_image_0.png

 

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

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

 

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;

 

Creator
Creator

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

Contributor
Contributor

Thank you! It worked just fine.