topic Re: How to exclude null values from data uploaded from a text file in QlikView

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

Wed, 11 Jul 2012 10:14:25 GMT

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

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

jjfabian — Wed, 11 Jul 2012 10:18:49 GMT

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

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

Anonymous — Wed, 11 Jul 2012 10:19:12 GMT

Marie,

A relaible way to check nulls and blanks is

len(trim(FieldName))=0

Regards,

Michael

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

nilesh_gangurde — Wed, 11 Jul 2012 10:21:33 GMT

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

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

nagaiank — Wed, 11 Jul 2012 18:30:56 GMT

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

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

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

Fri, 13 Jul 2012 15:30:41 GMT

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.

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

Fri, 13 Jul 2012 15:32:55 GMT

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.

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

Fri, 13 Jul 2012 15:37:46 GMT

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.

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

Fri, 13 Jul 2012 15:39:34 GMT

Thank you for the link,

Regards,

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

MayilVahanan — Fri, 13 Jul 2012 15:42:14 GMT

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...