Replace Null with 0

[chhavi376]

Hi all,

there is a excel file which has the tables to be loaded and is updated time to time.

A table is being created using file names present in the excel which is being loaded using LOOP. (All files left joined)

As i left join these files, the columns contain many null values.

I want to replace these null values to 0,for all the columns which are being pulled from the files.

I cannot use isnull() as i cannot specify each field explicitely.

PFB the code:

FileListTable:

LOAD [File_Name]

FROM [lib://AttachedFiles/Files.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vFileNo=1;

Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

Raw:

Load pat_id;

      SQL SELECT *

      FROM IMPALA.$(vFileName)

For vFileNo = 2 to NoOfRows('FileListTable')

     Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

      Left Join (Raw)

      Load *;

      SQL SELECT *

      FROM IMPALA.$(vFileName);

;

[tamilarasu]

Hi Chhavi,

Try,

NullAsValue *;

Set NullValue = 0;

FileListTable:

LOAD [File_Name]

FROM [lib://AttachedFiles/Files.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vFileNo=1;

Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

Raw:

Load pat_id;

      SQL SELECT *

      FROM IMPALA.$(vFileName)

For vFileNo = 2 to NoOfRows('FileListTable')

     Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

      Left Join (Raw)

      Load *;

      SQL SELECT *

      FROM IMPALA.$(vFileName);

[chhavi376]

Somehow this isnt working..

[tamilarasu]

Try this,

FileListTable:

LOAD [File_Name]

FROM [lib://AttachedFiles/Files.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vFileNo=1;

Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

Raw:

Load pat_id;

      SQL SELECT *

      FROM IMPALA.$(vFileName)

For vFileNo = 2 to NoOfRows('FileListTable')

    Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

      Left Join (Raw)

      Load *;

      SQL SELECT *

      FROM IMPALA.$(vFileName);

Next

//Null to 0

FOR t = 0 TO NoOfTables() -1

LET vTableName = TableName(t);

NullAsValue *;
Set NullValue = 0;

NoConcatenate
$(vTableName)_Temp:
Load *
Resident $(vTableName);

DROP Table $(vTableName);
RENAME Table $(vTableName)_Temp to $(vTableName);

NEXT

[chhavi376]

Still no change. I am not sure how is your code going to refer to the 'Raw' table made ?

[tamilarasu · Accepted solution]

Copy and paste full script that I have provided. If your data model is having only one table i.e Raw, then no need to use loop, you can use below script (Copy paste full script).

FileListTable:

LOAD [File_Name]

FROM [lib://AttachedFiles/Files.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vFileNo=1;

Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

Raw:

Load pat_id;

      SQL SELECT *

      FROM IMPALA.$(vFileName)

For vFileNo = 2 to NoOfRows('FileListTable')

    Let vFileName = Peek('File_Name',vFileNo-1,'FileListTable');

      Left Join (Raw)

      Load *;

      SQL SELECT *

      FROM IMPALA.$(vFileName);

//Null to 0

NullAsValue *;

Set NullValue = 0;

NoConcatenate

Raw_Temp:

Load *

Resident Raw;

DROP Table Raw;

RENAME Table Raw_Temp to Raw;

If you have multiple tables in your data model and wants to replace all the null values to 0 then use my previous code (that I have provided in my previous reply).

[chhavi376]

Finally !

Thanks a lot !