ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

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

ScottS28
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?

MarqueeCrew
19 - Altair
19 - Altair

@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.
ScottS28
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.

Merlain
5 - Atom

Hello

I was also surprised that adding no value to a number results in no value (Null)...

 

I have a question of logic though on this issue...

why

String + Null = String

and

number + Null = Null

then on Alteryx?

 

Thanks

Labels