Solved: Re: Replacing NULL Values - Qlik Community

pradeep92 · ‎2018-01-31

Hi,

I loaded a CSV File and an Excel file with Null Values. I wanted to interpret the null values and then select the null values , set it to MISSING.

ALso, SET NULLDISPLAY=''; is not working.. I would like to know the data source and scenario to use this ..

INTERPRETATION:

When I give,

SET Nullinterpret='';

Only the blank data in CSV file is set to '-'

How do i do it ? kindly help

Anonymous · ‎2018-02-01

Hi Pradeep

For some reason I don't know why, qlikview leads NULL values xls file different of xlsx.

But i found a way to get what you want.

Try SET the NullInterpret AFTER the xls load. Then, your script should be something like this:

NULLASVALUE *;

SET NullValue = 'MISSING';

Customer:

LOAD [Customer ID],

     Customer,

     Address,

     City,

     Zip,

     Country

FROM

[Customer.xls]

(biff, embedded labels, table is CUSTOMER$);

Set NullInterpret ='';

CSV Load:

...

I hope that works. here was OK!

View solution in original post

kaushiknsolanki · ‎2018-01-31

Check this link.

NULL handling in QlikView

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

pradeep92 · ‎2018-01-31

OK. What is the use of NULLDISPLAY?

kaushiknsolanki · ‎2018-01-31

NullDisplay is used to determine which value to be treated as Null, when you take data from ODBC connection.

NullInterpret is used to determine which value to be treated as Null, when you take data from files.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

pradeep92 · ‎2018-01-31

Hi Kaushik,

Now after loading both the CSV file and Excel file which has blank spaces ,

i have set up 2 scenarios:

1st:

SET nullinterpret=''; ---> Output: Null value in CSV File alone as(-)

2nd:

SET nullinterpret='Missing';---> Output: Null value in Excel file alone as(Missing)

Why i am not able to set for both altogether? Is there something wrong my declaration?

I also tried

NullAsValue *;

Set NullValue = 'NULL';

It sets the Excel file value as NULL and not the CSV file fieldname..

kaushiknsolanki · ‎2018-01-31

Hi,

This functions are used for the values of the field and not for the Fieldname.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

pradeep92 · ‎2018-01-31

Hi,

It is for the Field value in both in CSV and Excel and not the fieldname.

pradeep92 · ‎2018-01-31

any response?

kaushiknsolanki · ‎2018-01-31

Yes.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

pradeep92 · ‎2018-01-31

Hi Kaushik,

Now after loading both the CSV file and Excel file which has blank spaces ,

i have set up 2 scenarios:

1st:

SET nullinterpret=''; ---> Output: Null value in CSV File alone as(-)

2nd:

SET nullinterpret='Missing';---> Output: Null value in Excel file alone as(Missing)

Why i am not able to set for both altogether? Is there something wrong in my declaration?

I also tried

NullAsValue *;

Set NullValue = 'NULL';

It has to give me both field values as NULL but It sets the Excel file field value as NULL and not the CSV file Field value..