How to exclude null values from data uploaded from a text file

[#1]

Hello,

I've uploaded data from a text file containing several fields with missing values and I've tried in vain to discard the null values. When I try to do a pie chart, it doesn't exclude the missing fields.

I've tried SET NullInterpret= ' ';

I've also tried a null count but it doesn't work.

Thanks & regards,

Marie Joelle

[jjfabian]

Have tried using the "Supress when value is null" checkbox in the dimension tab of your chart?

[Anonymous]

Marie,

A relaible way to check nulls and blanks is

len(trim(FieldName))=0

Regards,

Michael

[nilesh_gangurde]

Hi Mijouana,

Add the new field at the load time.

your script will be as follows:

if(Isnull(FieldName),'Null', FieldName) as New_FieldName,

this will give you the text "Null" where values are null.

Regards,

Nilesh Gangurde

[nagaiank]

A general discussion on null value handling in Qlikview is here.

A sample data of your application will be helpful to suggest solutions.

[#6]

I've tried but it doesn't work. I think it might be because I've loaded 2 different types of files, an excel one and a text file and have merged them, using two common fields as key fields. So, all the blank values associated to those two fields cannot be discarded.

Thanks anyway for your reply.

MJ

[#7]

I've tried that one too but it doesn't work. Maybe because the missing values are associated to two key fields(I've merged two different files using same columns as key fields) ?

Thanks anyway for your reply.

MJ

[#8]

The null count returns a 0 value. I think that either this must be because the missing values are associated to two key fields(I've merged two different files using same columns as key fields), or there might be something wrong with my data.

Anyway, thanks for your help.

MJ

[#9]

Thank you for the link,

Regards,

MJ

[MayilVahanan]

HI,

     While loading itself, you can use like this,

     Load *, if(Len(Trim(fieldName)) > 0, filedName) from filename.xls;

     So, you can remove the null value in it..

(or)

if u want to set null value , use like this,

     Load *, if(Len(Trim(fieldName)) > 0, filedName,'Null') from filename.xls;

               (or)

     NullAsValue *;

     Set NullValue = '<NULL>';

Hope it helps...