Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

New Contributor

Replacing Zero with Null

Looking around it seems most folks want to replace their Nulls with something but I have a data set where all the missing values are set to Zero. If I then try and do any statistics on this I get ridiculous answers, so I would like to change all the zeros to Nulls at load. Is there a relatively straight forward way of doing this?

I can see where I could use a where statement to remove the entire row which contains a zero but I just want to exclude the individual values.


5 Replies
Valued Contributor II

Re: Replacing Zero with Null

try this in script

SET NullInterpret = 0;
SET NullValue = '-';


//Load your table and all Values with 0 will be replaced with '-'

//if you want to limit this to field just typ NULLASVALUE Field1, Field2;

New Contributor

Re: Replacing Zero with Null

Thanks for the fast response Tim. I put this at the top of the load script with all the other set commands but it didn't appear to do anything. Should I have put it somewhere else?

Also the excel file that I am importing the data from sees the numbers as text - QV is correctly interpreting them as numbers but I wonder if that has something to do with it.


Re: Replacing Zero with Null

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

you can use this NullAsValue

Honored Contributor II

Re: Replacing Zero with Null


you can also another way using alt function


i.e: alt( <field name> , <value_if_NULL> )

for the load script u can try

NullAsValue 'Field name';  // or NullAsValue *;

set NullValue='Unknown';



MVP & Luminary
MVP & Luminary

Re: Replacing Zero with Null

I think I wouldn't try to replace the zeros with NULL else I would fetch this within the expressions, for example with something like: count({ Field -= {0}>} Field)

- Marcus