Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Thanks

5 Replies
zhadrakas
Valued Contributor II

Re: Replacing Zero with Null

try this in script

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

NULLASVALUE *;

//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;

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

manu1512
Contributor

Re: Replacing Zero with Null

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

you can use this NullAsValue

devarasu07
Honored Contributor II

Re: Replacing Zero with Null

Hi,

you can also another way using alt function

alt(sum(Sales),0)

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


for the load script u can try

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

set NullValue='Unknown';

Thanks,

Deva

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