topic Re: Replacing NULL Values in QlikView

Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 07:38:41 GMT

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

Re: Replacing NULL Values

kaushiknsolanki — Wed, 31 Jan 2018 07:49:33 GMT

Check this link.

NULL handling in QlikView

Regards,

Kaushik Solanki

Re: Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 08:30:45 GMT

OK. What is the use of NULLDISPLAY?

Re: Replacing NULL Values

kaushiknsolanki — Wed, 31 Jan 2018 08:54:55 GMT

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

Re: Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 09:43:24 GMT

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

Re: Replacing NULL Values

kaushiknsolanki — Wed, 31 Jan 2018 09:46:11 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 10:47:21 GMT

Hi,

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

Re: Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 11:37:51 GMT

any response?

Re: Replacing NULL Values

kaushiknsolanki — Wed, 31 Jan 2018 11:43:12 GMT

Yes.

Regards,

Kaushik Solanki

Re: Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 13:04:17 GMT

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

Re: Replacing NULL Values

Anonymous — Wed, 31 Jan 2018 14:23:42 GMT

Hi all

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

Set NullInterpret ='';
NULLASVALUE *;
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".

Re: Replacing NULL Values

pradeep92 — Wed, 31 Jan 2018 14:47:32 GMT

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

Re: Replacing NULL Values

Anonymous — Wed, 31 Jan 2018 16:27:39 GMT

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.

Re: Replacing NULL Values

pradeep92 — Thu, 01 Feb 2018 08:58:25 GMT

Hi,

Here is the attachment ..

ZIP and COMMENTS are the Blank values.

Re: Replacing NULL Values

tamilarasu — Thu, 01 Feb 2018 09:21:37 GMT

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;

Re: Replacing NULL Values

tamilarasu — Thu, 01 Feb 2018 09:23:19 GMT

Hi Pradeep,

The below two lines are enough.

NullAsValue *;
SET NullValue = 'Missing';

Re: Replacing NULL Values

pradeep92 — Thu, 01 Feb 2018 10:10:06 GMT

When I give this,

NullAsValue *;

SET NullValue = 'Missing';

Only ZIP field contains Missing value and not the Comments field

Re: Replacing NULL Values

Anonymous — Thu, 01 Feb 2018 10:30:12 GMT

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!

Re: Replacing NULL Values

tamilarasu — Thu, 01 Feb 2018 10:32:20 GMT

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;

Re: Replacing NULL Values

pradeep92 — Thu, 01 Feb 2018 11:09:55 GMT

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