Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Converting Null value to zero

haya
8 - Asteroid

Hi Team,

 

Can you please let me know how to convert null values of a numeric field to zero as I am facing issue with division because of this (Something equivalent to coalesce function in sql).

Also can you let us know how to do vlookup in alteryx.

 

Regards,

Haya

14 REPLIES 14
LordNeilLord
15 - Aurora
haya
8 - Asteroid

Hi Thanks for your reply ,But I am looking for a function which converts null to 0.Is there any relevant function which is equivalent to coalesce.

 

Regards,

Haya

LordNeilLord
15 - Aurora

Does this not give you what you want?

 

Datacleanse.PNG

Rich_Stebles
8 - Asteroid

I think the original poster must mean the function that would be used within a Formula tool, not the Data Cleansing tool itself

 

This aspect of the Data Cleansing tool is essentially a Formula tool running this function:

 

IF IsNull([field]) THEN 0 ELSE [field] ENDIF

 

I think the original poster may be hoping for a simple function that automatically treats nulls as 0, without the need to run a Data Cleanse on the column first.  (Perhaps they want to preserve the nulls in the field).  So, something like this, for summing two fields that contain random nulls:

 

NoNull( [field1] ) + NoNull( [field2] )

 

I don't think there is such a method, which leaves the in-Formula tool solution as:

 

IF IsNull( [field1] ) THEN 0 ELSE [field1] ENDIF + IF IsNull( [field2] ) THEN 0 ELSE [field2] ENDIF

Rich_Stebles
8 - Asteroid

Further to my point above, I have discovered the ToNumber() function converts Nulls to 0 automatically, which in effect is the simplest function to handle adding fields together without pre-cleaning them

 

ToNumber( [Field1] ) + ToNumber( [Field2]) will add up the two fields correctly, regardless of Nulls:

 

clipboard_image_0.png

 

Regards

Richard

Alteryx_AR
12 - Quasar

Imputation tool can also be used to replace Null with any user specified value.

elsa2020
5 - Atom

I'm also having this issue. I'm using the data cleansing tool and after I select to remove nulls columns I still have null values after I run. 

mbarone
16 - Nebula
16 - Nebula

You can always use the ToNumber() function.  For example ToNumber([your_numeric_field_here]).   It will take nulls for a numeric field and set them to zero.

Rich_Stebles
8 - Asteroid

"I'm using the data cleansing tool and after I select to remove nulls columns I still have null values after I run"

 

It may be because your field is either a string or numeric but you have the option that handles that unticked:

 

 
Labels