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!

Alteryx Designer Desktop Discussions

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

Identify records with ToNumber Conversion Errors

JeremyN
8 - Asteroid

Hi,

 

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.  

 

Thanks!

2 REPLIES 2
DavidP
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.

 

DavidP_0-1582324241871.png

 

JeremyN
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.

 

Labels