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

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Different behaviours for conversion errors

Alteryx has different behaviours for conversion errors depending on the type of conversion desired. When converting from string to date data type, a conversion error will generate a NULL value. When converting from a string to a numeric data type, a conversion error will generate 0. Why the different behaviours? There is a lack of harmony here. 0 is a valid value and should not be the generated value for a failed string to numeric conversion. It should be NULL.

 

When I perform data type conversions, i do not apply them directly to the source field and then cast it. If there is a conversion error, then I have lost or corrupted the source information. Rather, I create a target field with the desired data type and use a formula to apply a conversion, such as datetimeparse or tonumber. Finally, I do a comparison of the source and target values. If the datetimeparse generated a NULL then I can PROGRAMMATICALLY address it in the workflow by flagging or doing some other logic. This isn't so easy to do with numerics because of the generated 0 value. If I compare a string "arbitrary" to the generated 0 value as a string then clearly these do not match. However, if I compare a scientific value in a string to the converted numeric as a string, then these do not match though they should. My test of the conversion shows a false positive.

 

I want a unified and harmonised conversion behaviour. If the conversion fails, generate a NULL across the board please. If I am missing something here and people actually like conversion errors to generate 0 please let me know.

1 Comment
Alteryx Certified Partner

I have a workaround until Alteryx resolves this. I know that a string to number conversion error will result in a 0. I am performing conditional logic with regex to check the conversion, as shown below where source_field is the string and target_field is the converted number:

 

IF [target_field] = 0
THEN IF
REGEX_Match([source_field], '\$?0*\.?0+')
THEN Null()
ELSE 'conversion error'
ENDIF
ELSE Null()
ENDIF