Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?]
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,
...
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.
Yes, I tried that, but when I click on the "Reload" I get the following error message:
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?]
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;
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;
if(match([Status],'New','Current'),[Status],'unknown') as CAlDim ;
Thank you! It worked just fine.