topic Re: Remove Null values in the script itself in QlikView

Remove Null values in the script itself

Fri, 10 Jun 2011 07:03:40 GMT

Good Morning All,

In the script I am loading Name, Id, School, Fees. In the Name I have some null values.

What I need is I would like to remove the null values in the script itself, I tried Name<> null, its not working for me.

Can anybody suggest a solution.

Regards

Joe

Remove Null values in the script itself

Fri, 10 Jun 2011 07:10:44 GMT

try to use the isnull function.

If you just want to replace the nulls with values use this:

if ( isnull( <field>), <replacement for null value>, <field> )

Remove Null values in the script itself

Fri, 10 Jun 2011 07:23:33 GMT

Good morning Joe,

you could use a WHERE-Statement with the isnull()-function. Let me give you an example:

table:

LOAD

name,

date,

title

FROM source

WHERE isnull(date)=0;

This will keep all datasets out of "table" that contain a nullvalue in the date-field.

Does this solve your problem? If not, please let me know.

Regards,

Martin

Remove Null values in the script itself

pat_agen — Fri, 10 Jun 2011 07:34:52 GMT

hi Joe,

in the 64bit environment the isnull() function sometimes can be tricky. I've had cases where reading from xl it doesn't always pick up "empty" cells.

look around the forum and you will see people often prefer to use the test len(field)=0.

ChristianDahlke has shown you how to replace the field in your record whilst keeping the record

if(len(Name)=0,'name missing', Name) as Name

whereas laidig's solution will remove the whole record

from source

where len(Name)>0

Remove Null values in the script itself

Fri, 10 Jun 2011 15:01:44 GMT

Sometimes what I do is just load another table (NOCONCATENATE LOAD) and add a where clause like:

WHERE len([Field that might be null])>0;

DROP TABLES (first_table_name);

Not sure if this is the best way or not but it works for me.

Re: Remove Null values in the script itself

ramchalla — Mon, 22 Aug 2016 07:41:40 GMT

Hi Vincent,

You can use the below code in your script. it will restrict the null values to get populated.

Directory;

LOAD name,

date,

title

FROM

b.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(name))>0;