Learn more about the Alteryx Maveryx Universe recently announced at Inspire 2023!

Alteryx Designer Desktop Discussions

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

Identify records with ToNumber Conversion Errors

8 - Asteroid



I would like to have a check for non-numeric values in a numeric field.  I would like this to apply to decimals, values with commas, and scientific notation (E in the field).  As far as I can tell the ToNumber formula would list any of the errors I am looking to identify as conversion errors.  Is there a way to add information from conversion errors log to the data itself?  I've tried other techniques such as a data cleanse tool to remove characters from a field and comparing to the original field, but this doesn't work for scientific notation.  



17 - Castor
17 - Castor

Just make sure when using ToNumber that you include the optional parameters. By default conversion errors are converted to zero. 


I don't know that there is a way to include conversion errors in the data, but you can at least force those fields to be null.




8 - Asteroid

The best solution I've found is using a data cleanse tool to remove characters from a field, comparing to the original field to look for differences, and checking for a value of 'E' indicating scientific notation in the original field.  Formula would look like the following.


([Value] != [Value W Alpha Removed]) and NOT Contains(Uppercase([Value]), "E")


This will identify values such as 100k.