Alteryx Designer Discussions

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

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live!

[Null] + Number Yields [Null]: Auto-cast to Zero?

Highlighted
8 - Asteroid

This is not a bug but figured I'd check just in case anyone has some thoughts or insights.

 

If I have two numeric fields, add them together, and store the result in a third, the resultant field will be null whenever either of the two addends are null. I'm sure this is working as intended but catches me by surprise -- I'd expect the result to be 0 if both fields are null, or else if only one field is null, to be the value of the other field. (Unfortunately I've developed this habit/expectation from working in other languages / environments that automatically cast null values to 0 when performing numeric arithmetic.)

 

I know I can either convert the null values to 0 before the addition, or else change the formula for the definition of the third field to check for nulls in either addend. (Or probably several other ways to resolve!) I just often forget to do this proactively, and I've lost a few cumulative hours at this point in my career debugging this issue across multiple workflows. I also don't like using the Cleanse tool on numeric fields because null and 0 often have different meanings, so I prefer to preserve the null values.

 

Any way to achieve this "auto cast" behavior or do I just have to make sure that I'm always careful around null values?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@ScottS28 ,

 

True.  That's what you get for adding Null values.

 

Max([Field1],0) + Max([Field2],0)

 

You can either replace all Nulls with 0 (you can use the Data Cleansing macro) or use a formula like the one above.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
8 - Asteroid

Thanks for the confirmation! I did figure this was simply part of the
definition of the data type of null, but it’s interesting that the max
function is well behaved with respect to null when the addition operator is
not.

Labels