topic Re: Replacing Zero with Null in QlikView

Replacing Zero with Null

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Replacing Zero with Null

zhadrakas — Tue, 16 Jan 2018 10:44:46 GMT

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;

Re: Replacing Zero with Null

Anonymous — Tue, 16 Jan 2018 10:57:07 GMT

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

manu1512 — Tue, 16 Jan 2018 11:01:42 GMT

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

you can use this NullAsValue

Re: Replacing Zero with Null

devarasu07 — Tue, 16 Jan 2018 11:02:29 GMT

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

Re: Replacing Zero with Null

marcus_sommer — Tue, 16 Jan 2018 11:52:10 GMT

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