Replace Blanks and Nulls On Load

[rebelfox]

I am loading a table with a field that can have a NULL, blank spaces or a unique value.

On the LOAD statement I convert the NULL to 'None'.

MyTable1:

OUTER JOIN LOAD

    MyField1,

    MyField2,

    MyField3

    IF(ISNULL(MyField4),'None',MyField4) As My_Code

RESIDENT MyTable2;

I want to also convert blank spaces to 'None'.

Is there a better way of doing it than embedding two IF statements.

I think it's a bit hard to read.

IF(MyField4 = ‘ ‘,None’,IF(ISNULL(MyField4),'None',MyField4)) As My_Code

[marcus_sommer]

In this cases you could use: if(len(trim(MyField4))=0, 'None',MyField4)

- Marcus

[Gabriel]

Hi,

What you can do is to convert NULL value in the script

This code has to be before MyTable1:

Map_Nulls:
Mapping LOAD
NULL(),
‘<Unknown>’
AutoGenerate 1;

And this after MyTable1:

MAP FieldName, * using Map_Nulls;

[simenkg]

if(len(trim(MyField4))=0, None, myField4) as My_Code

[sasiparupudi1]

if (len(trim(MyField4))=0,'None',MyField4) as My_Code

[PrashantSangle]

Hi,

try

IF(ISNULL(MyField4) or len(trim(MyField4))=0,'None',MyField4)

Regards

[Anonymous]

Hi,

Use this

if(len(trim(MyField4))=0,'None',MyField4) AS My_Code

Hope it helps!!