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 Knowledge Base

Definitive answers from Designer Desktop experts.

Converting strings to numbers

HenrietteH
Alteryx
Alteryx
Created

Sometimes, a dataset will contain numbers stored as text. I order to do calculations using those numbers, the datatype will need to be converrted to a numeric data type.

 

If the data is clean, changing the data type in a select tool can do the trick.

 

Another option is to use the TONUMBER() function in a formula tool or multi field formula tool (if you have more than one field to convert).

 

TONUMBER(x, bIgnoreErrors, keepNulls)

 

x: the column you are converting to a number

bIgnoreErrors and keelNulls are optional parameters.

bIgnoreErrors: 0 or false (default) means it will report conversion error messages; 1 or true means it will ignore conversion errors.
keepNulls: 0 or false (default) means it will convert non-numeric values (including null) to zero; 1 or true means it will convert non-numeric values to null.

This option can be very helpful if you want to treat nulls differently than zero in your dataset.

 

 

Now, what if your data is messy? What if users added leading zeros, currency symbols, etc?

You can use the formula tool to clean up your data before converting it to numbers.

 

Functions you can use to clean data:

REPLACECHAR(x, y, z): Returns the string [x] after replacing each occurrence of the character [y] with the character [z].

 

REGEX_REPLACE(string, pattern, replace,icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string. Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier. The replace parameter can be either a specified value as shown below, or a marked group, such as "$1"
The icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.

 

TRIM(x, y): Remove the character(s) in the string y from the ends of the string x. Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.

 

 

Also see the attached workflow for examples of how to use the Formula tool to clean data before converting it to a number.

 

Attachments
Comments
srikant2017
5 - Atom

For me the conversion for string to number or number to string did not work ...SELECT command came in handy to change the way I want input to be 

ilotvin
7 - Meteor

Hello all,

Regarding data conversion from string to Int.

My data set contains numbers stored as text. 

When I try to convert either with Select or Formula tool Alteryx treats the first comma as decimal number

Before conversion 31,396,212.06 (V_WString)

After Conversion 31 (Double, Int and all other numeric options)

I'll appreciate your help.

Thanks in advance.

 

RodLight
8 - Asteroid

@ilotvin

This would be considered "messy data", so use the function mentioned by @HenrietteH above...

REPLACECHAR(your_field, ",", "")

...to get rid of the commas first.

Then you can convert to a number.

bluebrent
7 - Meteor
hey, I am wondering what the best way to code in a letter-number cipher. (i.e. A=1, B=2, C=3,...) My data currently exists as an 8 character string, with 7 digits, and the last value being a number. This string is supposed to represent an 8 digit number, where the last digit is converted to a letter to mark the end of the column (from a very old data set in a flat file). Any advice? tia
DataKiller
7 - Meteor

If have many columns want to convert from string to number, how to do it? 

DataMeister
7 - Meteor

As Srikant said in 2017, the Select Tool is supposed to change strings to numbers and vice versa. That's why it has the datatype column, righhht? Then why are there so many posts (going on for years) about how that tool doesn't work? I can't get it to change a number to string either, and have to resort to absurd functions and formulas--which totally negate the drag & drop promises of Alteryx! Look, the oil industry uses API numbers like 33053066770000 and although numerals, they're not numbers that will be added, subtracted, or multiplied. So they're basically treated as strings. Sooo, let's get some developers on the task of making the user-friendly Select tool do its job.

Cal_A
7 - Meteor

I agree with Datameister

 

Alteryx should not expect users to use regex to handle strings like " 31,396,212.06 " . This is not some esoteric proprietary format.

 

I can understand a reluctance to to this automatically via select tool.  But the ToNumber() function or a number/currency parsing tool should exist at a minimum. It could allow users to review/confirm assumptions about decimal and thousands separators.

 

You know what, you could even chuck it in the Auto Field tool. Just add some parameters for automatic ccy/number conversion.

lepome
Alteryx Alumni (Retired)

@Cal_A 

Is there something prohibitively difficult about ToNumber(Replace([Field1],',',''))

(This is sarcasm--I fully recognize that using a Replace() is functionally equivalent to using a REGEX_Replace() and is therefore an absurd distinction.)

 

Well, in 2020.4 you don't need to use the Replace function.  ToNumber() just does it.  BUT, there are specific steps you must take when you upgrade to 2020.4.

 

 

 

Cal_A
7 - Meteor

@lepome 

 

Perhaps you should review the wording you used and the sneering tone it portrays towards Alteryx customers. What a fantastic way to respond to people in the Alteryx community making suggestions for improvement.

 

1) The requirement to manually code for something as simple as recognition of numbers and currency is at odds with how Alteryx sells itself and indeed at odds with the level of automation in its other functionality.  Heck, it is at odds with competitor products too.

 

2) Point 1 is somewhat proven by the fact that the poor functionality has been addressed in 2020.4 - i.e. someone obviously agrees that it is a shortcoming and fixed it. Incidentally, this doesn't help me, and it won't help anyone else in a very large organisation who will get the upgrade when IT deems it is time.

 

And finally to answer your original question/insult.  No, for me it is not difficult, it is just a waste of time and the sort of thing that keeps me using python/pandas.  But Alteryx is being pushed in my organisation to many who don't have my background and the point is to make things easier for them.

 

Hopefully you can learn something from this interaction and apply it to the next customer you address.  I would prefer if you keep any further thoughts you have to yourself.

 

 

 

 

lepome
Alteryx Alumni (Retired)

@Cal_A 

I apologize.  I thought the sarcasm would be interpreted as the sympathetic agreement I feel.  My intent was not to insult.  (I annotated my prior post and sincerely regret that lapse in my literary judgment.)

 

Regardless, I thought that with the caveat that upgrading to 2020.4 is not as simple as a usual upgrade, it is relevant that (although you'll still need to use a Formula tool rather than just a Select tool) the situation is now somewhat improved.