Welcome to the second Alteryx related post, this time we’ll look at one of the most common tasks when dealing with data. Find and Replace.
You may have records that you want to turn into Nulls or change the text of certain records. The first time I googled “Alteryx Find and Replace” I came across a number of posts referring to REGEX. Regex stands for regular expressions which sends chills down the spine of many out there. But as you know this a series to solve problems and to help you make the most of Alteryx at work, not to explain everything about a function. Also I don’t fully grasp REGEX yet myself. 🙂
Problem: I have two columns where I want to replace some of the text in specific records.
Here’s the workflow we’ll look at today.
The input I’m using is the very famous Tableau data source “superstore”.
First thing I’d like to do is to limit the number of columns I’m working with and for that we use a Select tool.
You do this by connecting the select tool to the input and uncheck the columns you don’t need to see. The result is below:
I have thought of 3 examples where we might want to use a replace function.
Example 1 – I’d like to change “Not Specified” in Order Priority to Null.
The way to do this is to connect the REGEX tool and change
- Field to Parse = the column you want to change
- Regular Expression = ^Not Specified
- The little hat indicates the start of the string and the text after, the part you want to remove.
- Output Method = Replace
- Replacement Text = Leave empty
Example 2 – I’d like to find records where Ship Mode is “Regular Air” and replace them with Airplane
This is rather similar to the example 1, the bit that changes is that this time you set a replacement text.
Example 3: I’d like to replace Air with Airplace, but to keep whatever word preceding Air untouched.
We have two types of Ship Mode that will be affected, “Regular Air” and “Express Air”, of course we could do it in two steps using the example 2, but it’s easier to do it in 1 step. See below
Regular Expression – ()? is a condition and it sets it as field 1 or $1, however we didn’t set a value so Alteryx will look for any value, which precedes the second declared value (Air).
Replacement Text – We want whatever word is found to be preceding “Air” to be untouched so the replacement text is $1, followed by the second field intended to replace Air, in this case Airplane.
This is the end result
Hopefully you can use some of this when you are asked to find a replace values within a string.
Also thanks to James Dunkerley for his help and getting me started on using regex.
Thank you for reading