Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Converting text to number when the ToNumber() function isn't ideal

Alteryx
Alteryx
Created

A flat file was parsed into a number of fields, including one field that needs to be converted to a numeric data type. 

 

It looks like this in the data: 

000000437
000012587

 

The resulting number is supposed to have four decimal points, so just applying ToNumber([Field]) won't work because it will convert 000000437 to 437 but the required output is 0.0437. 

  

As always, there are a couple different ways of going about this: 

 

1. Using the Substring(x, start, length) function, which returns the substring of [x] starting at [start], stopping after [length]. This way, the string can be broken into the first 5 characters and the last 4 characters and a decimal point inserted in between the two. Then, the ToNumber() function can be used as usual. 

 

2. Using the ToNumber(x, bIgnoreErrors, keepNulls) functions creatively. ToNumber will return 437 for the first row of data and 12587 for the second one. However, if the number is then divided by 10,000, it will return 0.0437 which is the correct result.

 

3. Using Regular Expression. Regex can recognize a pattern and replace it with another value. In this case, regex can find the last 4 characters of the string: (\d{4})$ and replace them with a decimal point + the pattern it matched to: \.$1

 


formula1.JPG

 

 

 

 

Also take a look at the attached workflow to illustrate the solutions. 

Attachments
Comments
Great, thanks