Replacing Nulls

[sushantk]

My problem is quite simple, but as I am new to Alteryx, I could use a little help.
There are certain fields which has null value in it
Can you suggest me a tool to replace null value with zero (0).

[BenMoss · Accepted solution]

Several options available to you.

1) use a formula tool

if isnull([field]) then 0 else field endif

If you want to apply this rule to mutliple columns check out the multifield formula tool.

2) the impute tool allows you to replace null values with a user defined value. This tool allows you to apply too many columns

3) data cleansing tool allows you to convert nulls to zeros for numeric field types. Again you can multi-select fields with this tool.

Ben

[mbarone]

My favorite trick for this, assuming the field is a numeric field, is a formula tool (or multi-field formula tool if more than one field), with the ToNumber() function wrapped around it:

ToNumber([field1]).  

That will change nulls to zero.

[tlarsen7572]

I abhor null values and wanted a very quick and easy way to remove them.  I started with a Multi-Field Formula tool using BenMoss's formula: if isnull([field]) then 0 else field endif

But this required 2 Multi-Field Formula tools if I wanted to clean both strings and numbers.  This was 1 tool too many for my tastes.  So I created a C++ formula add-in that will clean strings and numbers with 1 function.  It works wonderfully in our automated production analytics.  It may be overkill for your needs, but I thought I would offer a link:

https://github.com/tlarsen7572/cleannulls

Tom

[novice1]

what formula can  use to replace null with certain number?

[mbarone]

@novice1 ,

I like to use a Boolean conditional for that, in addition to the IsEmpty operator instead of IsNull (to catch both Null and Empty Strings which often look like Nulls but aren't):

IIF(IsEmpty([your_field_name]),whatever number you want,[your_field_name]).\

For example, if I want nulls replaced with the number 7:

IIF(IsEmpty([your_field_name]),7,[your_field_name]).