topic Re: What is NullAsValue and NullAsNull ?? in QlikView

What is NullAsValue and NullAsNull ??

nikhilgarg — Fri, 14 Nov 2014 05:39:49 GMT

Hey,

Can any one tell me what is :

NullAsValue and NullAsNull ??

And also the difference between them ??

I read reference manual also but didn't understand anything.

Thanks.

Re: What is NullAsValue and NullAsNull ??

its_anandrjs — Fri, 14 Nov 2014 05:47:59 GMT

Hi,

From QV Help it will well explained

NullAsValue

The NullAsValue statement specifies for which fields the encountered NULLs should be converted to values.

By default, QlikView considers NULL values to be missing or undefined entities. However, certain database contexts imply that NULL values are to be considered as special values rather than simply missing values. The fact that NULL values are normally not allowed to link to other NULL values can be suspended by means of the NullAsValue statement.

The NullAsValue statement operates as a switch and will operate on subsequent loading statements. It can be switched off again by means of the NullAsNull statement.

The NullAsValue can be combined with the variable NullValue that specifies which string to use as NULL value. If the variable NullValue is not used, NullAsValue will replace NULLs with empty strings.

The syntax is:

NullAsValue*fieldlist

*fieldlist is a comma separated list of the fields for which NullAsValue should be turned on. Using * as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field names may be necessary when wildcards are used.

Example:

NullAsValue A,B;

Set NullValue = 'NULL' ;

Load A,B from x.csv;

NullAsNull

The NullAsNull statement turns off the conversion of NULLs to string values previously set by a NullAsValue statement.

The NullAsValue statement operates as a switch and can be turned on or off several times in the script, using either a NullAsValue or a NullAsNull statement.

The syntax is:

NullAsNull *fieldlist

*fieldlist is a comma separated list of the fields for which NullAsValue should be turned off. Using * as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field names may be necessary when wildcards are used.

Examples:

NullAsNull A,B;

Load A,B from x.csv;

Regards

Anand

Re: What is NullAsValue and NullAsNull ??

nikhilgarg — Fri, 14 Nov 2014 05:51:11 GMT

Hey,

Do you mean to say that by NulAsValue the field for which it is set Null will treat it a value. And the fields for which NullAsNull is set will not treat the Null value if a field as value ??

Is it so ??

Re: What is NullAsValue and NullAsNull ??

SergeyMak — Fri, 14 Nov 2014 06:04:25 GMT

Hi Nikhil,

If you set

NullAsValue you can select just empty value in the table or in the listbox,

But if you set NullAsNull you will not see empty value in the listbox, you will see '-' sign in the table and you will not be able to select it.

Regards,

Sergey

Re: What is NullAsValue and NullAsNull ??

SergeyMak — Fri, 14 Nov 2014 06:10:54 GMT

PFA an application. Probably It will make clearer the difference.

The fields B and NullAsValue loaded as NullAsValue;

The fields A and NullAsNull loaded as NullAsNull;

Re: What is NullAsValue and NullAsNull ??

nikhilgarg — Fri, 14 Nov 2014 06:13:59 GMT

Hey,

You mean to say that let say we have 2 columns A and B.

If some values in column A is Null or Empty then ,

If i use:

NullAsValue A,B;

Set NullValue = 'NULL' ;

Load A,B from x.csv;

then , empty or null values will be presented as Null.

If i use NullAsNull then , the values which are empty or null will be shown by '-'

Is it so ??? Please clarify.

Thanks

Re: What is NullAsValue and NullAsNull ??

nikhilgarg — Fri, 14 Nov 2014 07:27:44 GMT

Hey,

I am using personal version of Qlikview so cannot open your attachement. Can you please tell me here.

Thanks

Re: What is NullAsValue and NullAsNull ??

SergeyMak — Fri, 14 Nov 2014 07:46:33 GMT

Put it into Load script and reload the app

NULLASNULL NullAsNull;
NULLASVALUE NullAsValue, B;

Data:
LOAD *,
Null() AS NullAsValue,
Null() AS NullAsNull

INLINE [
A, B
A,B
];

Concatenate(Data)
LOAD
'A' AS NullAsValue,
'A' AS NullAsNull
Resident Data;

Then, add listboxes of al fields and tableaux with all the fields.

You will see the result

Regards,

Sergey

Re: What is NullAsValue and NullAsNull ??

Brett_Bleess — Fri, 14 Nov 2014 12:58:50 GMT

Nikhil,

Try the following two links, they may help, and there is also a technical brief attached to them that may also provide further information that may help here.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/02/finding-null

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/30/null-the-invisible-nothing

Hope this helps you get a better grasp on things.

Regards,

Brett