Remove Null values in the script itself

[#1]

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

[#2]

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

[#3]

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

[pat_agen]

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

[#5]

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.

[ramchalla]

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;