Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

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.

4 Comments
seven
12 - Quasar

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

seven
12 - Quasar

According to the documentation for the ToNumber function, there is an optional argument keepNulls. It is set by default to 0 / False which means that if a string conversion results in a Conversion Error, the resulting value will be the number 0. All this time, I could have used it with a 1 / True which means that if a string conversion results in a Conversion Error, the resulting value with a NULL. Why didn't anyone tell me? All this time...

patrick_digan
17 - Castor
17 - Castor

@seven Thanks for passing along that note about the tonumber function, very useful.

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes