Replacing Zero with Null

[Anonymous]

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

[zhadrakas]

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;

[Anonymous]

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]

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

you can use this NullAsValue

[devarasu07]

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

[marcus_sommer]

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