Converting Null value to zero

[haya]

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

[LordNeilLord]

@haya,

For null to zero check out https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Data-Cleansing/ta-p/31753

For VLOOKUPs check out https://www.alteryx.com/alteryx-for-excel

[haya]

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]

Does this not give you what you want?

[Rich_Stebles]

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]

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:

Regards

Richard

[Alteryx_AR]

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

[elsa2020]

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]

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]

"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: