Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!

Alteryx Designer Desktop Discussions

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

Replacing Null values with text

8 - Asteroid

Hi there,


I've about 20 columns that contain Nulls here and there and I want to replace them with 'No Value'.  They are inputs to a predictive model and so I don't want to use 0s.


The Imputation tool only seems to enable replacing Nulls with a value.  Is there an equivalent tool for searching and replacing values with text that works across multiple variables and all rows?


Thanks in advance,


ACE Emeritus
ACE Emeritus

Hi Paul,


Yes this is possible using the multi-field formula tool which allows you to apply a formula,in your case something like

if isnull([_CurrentField_]) then "No Value" else [_CurrentiField_] endif

, to multiple columns.

Given that your current field type is likely to be a double then you would have to also use the 'change output type' select box also.

However you should be aware that it is unlikely that the predictive tools would then work on these fields as (depending on which tool you use), they are likely to require a field of numeric type.

Personally I would just leave them as NULLs.


11 - Bolide

You can add "No Value" to the field to replace the Nulls, but to do so you would convert the field from a number to a string.  This would make the predictive model handle this data very differently and potentially disrupt how you plan to use the data.  It would see the fields as categorical, no longer as continuous.

5 - Atom

This was very helpful. Thank you!

5 - Atom

You would lose the ability to treat the field as a numeric variable; it could be treated as a categorical variable.  Depending on the variable, you could bin the other values into categories.  If you can set the missing flag as the reference level in your model, then the the coefficients of the other categories would be the 'effect' between each category level and 'missing'.  This would be a good first look at the data.



7 - Meteor

Thanks Guys! This made my day! Appreciated!!!!

6 - Meteoroid

Thanks, Ben.  The formula logic was a great help to me when applied to another context.

5 - Atom

Great Idea BenMoss :)