WeeklyAlteryxTips#35　How to update the value with changing the field type

This is a basic tips.

Question

Supposed to create a field with the same name but with a numeric type, by using the Formula tool to convert a field that contains both strings and numbers into numbers only.

Input :

Output:

Solution 1 : Use the Formula tool and the Select tool

The generic way is to use the Formula tool and the Select tool. This is not the tips which I want to introduce but should know it. So, in this case, since I want to leave only numeric text, I use the REGEX_Replace to remove the unnecessary string. However, this RegEX expression is not for general purpose because I know the unnecessary string pattern and only remove those strings.

REGEX_Replace([Value], "[A-Za-z-/]", "")

The setting of the Select tool is as follows.

Or you can use the Data Cleansing tool insted of the Formula tool.

But the Data Cleansing tool is slow, so I recommend to use the CRew Cleanse. But in this post, I’m going to use Data Cleansing tool.

Finally, you got the data below.

Solution 2: Use the Multi-Field Formula tool

For the workflow like the solution 1, you can use a single Multi Field Formula tool to do it easily. Check the fields which you want to change the data type of the fields and to apply the same expression. And also, check the “Change Output Type to” option and select the data type which you want to change to. Additionaly, when you want to change into string type of the field and it doesn’t have enough string size, you can change the size.

Next, you look at the expression text box. When you want to apply the expression to a single field, you can write the field name directly, if not, you want to apply it to multiple fields, you can use [_CurrentField_] instead of the specific field name.

At this time, I want to apply the expression to a single field, so I write the specific field name directly. And I change the string type to numeric, so I use ToNumber function to change the data type. In this case, if you don’t use the ToNumber function, it works. But the Designer show the Warning as follows.

Conclusion

• I introduced how to calculation and to change the data type of the field at the same time.
• You can use the Multi Field Formula to do it easily.

Sample Workflow Download

The Next Blog Post is…

It will be about changing the field type.