Replacing NULL Values

[pradeep92]

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]

Hi all

You have to do 3 steps for handling all your NULLs together.

1. Set NullInterpret ='';
2. NULLASVALUE *;
3. SET NullValue = 'MISSING';

Explaining the steps above:

1. Note that '' are 2 single quotes, and not 1 double quote. With this, qlikview will interpret the CSV null value as NULL.

2. We set all fields who have NULL to have a "Value".

3. We change the NULL value to some "selectable".

[pradeep92]

As you told, I have given this.. but I can See MISSING only in CSV file and not in Excel File field value..

[Anonymous]

Hummm... That's weird.

Could you attach the XLS and CSV files here?

Follow, attached my examples. Look at then and let me know if work in your environment.

[pradeep92]

Hi,

Here is the attachment ..

ZIP and COMMENTS are the Blank values.

[tamilarasu]

Hi Pradeep,

Try this,

NullAsValue *;
SET NullValue = 'Missing';

A:
LOAD Postcode,
Locality,
State,
Comments,
DeliveryOffice,
PresortIndicator,     
Longitude
FROM
[..\..\..\QLIKVIEW_QLIKSENSE\Data+ QV practice\aust_postcode_lat_long.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Customer:
LOAD [Customer ID],
Customer,
Address,
City,
Zip,
Country
FROM
Customer.xls
(biff, embedded labels, table is CUSTOMER$);

Interval_match:
Load * Inline [
Start,End,Description
0,100,0-100
101,200,101-200
201,300,201-300
301,500,301-500
501,1000,>500
1001,2000,>1000
];

inner join
intervalmatch(PresortIndicator)
new:
Load Start,End Resident Interval_match;

[tamilarasu]

Hi Pradeep,

The below two lines are enough.

NullAsValue *;
SET NullValue = 'Missing';

[pradeep92]

When I give this,

NullAsValue *;

SET NullValue = 'Missing';

Only ZIP field contains Missing value and not the Comments field

[Anonymous · Accepted solution]

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!

[tamilarasu]

Hi Pradeep,

Not sure about this behavior but this is working for me.

NullAsValue *;

set NullInterpret='';

SET NullValue = 'Missing';

A:
LOAD Postcode,
Locality,
State,
Comments,
DeliveryOffice,
PresortIndicator,     
Longitude
FROM
[..\..\..\QLIKVIEW_QLIKSENSE\Data+ QV practice\aust_postcode_lat_long.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

set NullInterpret=;

Customer:
LOAD [Customer ID],
Customer,
Address,
City,
Zip,
Country
FROM
Customer.xls
(biff, embedded labels, table is CUSTOMER$);

Interval_match:
Load * Inline [
Start,End,Description
0,100,0-100
101,200,101-200
201,300,201-300
301,500,301-500
501,1000,>500
1001,2000,>1000
];

inner join
intervalmatch(PresortIndicator)
new:
Load Start,End Resident Interval_match;

[pradeep92]

yeah it is working Thank you for the response .. Means a lot!