topic Re: Null Values in QlikView

Null Values

Mon, 02 Jun 2014 10:11:02 GMT

Hi i have a table in that one field containing NULL Values on that place i want to show 'NULL' string in the table how can i do

can any one please help me.

Re: Null Values

tresB — Mon, 02 Jun 2014 10:14:05 GMT

In the script:

NullAsValue FieldA, fieldB;

Set NullValue = 'NULL' ;

for details: NULL handling in QlikView

Re: Null Values

Mon, 02 Jun 2014 10:18:18 GMT

Sorry can you please explain some more , i can't understated.

Re: Null Values

tresB — Mon, 02 Jun 2014 10:22:25 GMT

Please have patience and go through the pdf you find in the provided link. That is a very nice and detailed document that teaches you all about NULL and how to handle them. Otherwise, post sample qvw with your query.

Re: Null Values

Mon, 02 Jun 2014 10:26:11 GMT

In the above table i want to use the NULL string in the null value place

Re: Null Values

maxgro — Mon, 02 Jun 2014 10:36:13 GMT

from online help

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;

before loading your table with null values in field FieldWithNull

NullAsValue FieldWithNull ;

Set NullValue = 'NULL' ;

Load

FieldWithNull ,

Field2,

.......

Fieldn

from .......;

a different way

Load

if(len(trim(FieldWithNull))=0, 'NULL', FieldWithNull) as FieldWithNull,

Field2,

.......

Fieldn

from .......;

hope it helps

Re: Null Values

tresB — Mon, 02 Jun 2014 10:37:53 GMT

Reload your application after adding those two lines in the script:

Re: Null Values

maxgro — Mon, 02 Jun 2014 10:41:32 GMT

with your data

LOAD

......

if(len(trim([Functional Location]))=0, 'NULL', [Functional Location]) as [Functional Location],

if(len(trim(Capacity))=0, 'NULL', Capacity) as Capacity,

....

FROM

.....

;

Re: Null Values

Anonymous — Mon, 02 Jun 2014 10:43:57 GMT

Go to Properties ->Presentation->Null Symbol->Replace '-' by 'NULL'