community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Replacing Null values with text

Highlighted
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,

Paul

Alteryx Certified Partner
Alteryx Certified Partner

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.

Ben

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.

Atom

This was very helpful. Thank you!

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.

 

 

Labels