topic Re: Replace Null with 0 in QlikView

Replace Null with 0

chhavi376 — Mon, 05 Feb 2018 08:23:31 GMT

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

;

Re: Replace Null with 0

tamilarasu — Mon, 05 Feb 2018 08:31:06 GMT

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

Re: Replace Null with 0

chhavi376 — Mon, 05 Feb 2018 08:35:44 GMT

Somehow this isnt working..

Re: Replace Null with 0

tamilarasu — Mon, 05 Feb 2018 09:07:55 GMT

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

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

Re: Replace Null with 0

chhavi376 — Mon, 05 Feb 2018 09:24:46 GMT

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

Re: Replace Null with 0

tamilarasu — Mon, 05 Feb 2018 09:43:54 GMT

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

Re: Replace Null with 0

chhavi376 — Mon, 05 Feb 2018 09:53:31 GMT

Finally !

Thanks a lot !