Data analysts often need to prepare data in a certain way. Parsing, or separating data from one column to a different column is a common occurrence and there are different methods in Alteryx to solve this. One problem of interest in this blog is to find and extract values in string records that match any string values from a separate column. For instance, in the Alteryx weekly challenge 286, a column called “Article Title” contains the title of articles on the events of electrical outages caused by critters/animals, such as “Power outages blamed on bats”. The goal is to extract the animal from the article title (in this case, “bat”) into a new column called “Animal List”. The list of animals (such as “bat”) that caused the electrical outage has been created beforehand in a separate column called “Animal List”. Note that the column name of this list matches the new column that will be created to contain the extract from the “Article Title”.

 

This process could be lengthy to perform with tools such as Regex if the list of values is long and has no particular pattern. One simple solution is to utilise the “Find Replace” tool which can be found under the “Join” tool category.

Here’s how you can use the “Find and Replace” tool in Alteryx:

 

1.) Drag and drop the “Find Replace” tool onto your Alteryx workflow canvas.

2.) Connect the input data containing the column that we want to extract from (in this case the “Article Title”) to the “F” receptor of the “Find Replace” Then connect the table containing the list of values that we want to match (“Animal List”) to the “R” receptor.

 

3.) Input the column name from which we want to extract the data in the “Find within field” In this example, the input would be the “Article Title”.

4.) Input the list of values that we want to find in the “Find value” In this example, the input would be the “Animal List”.

5.) Configure the particular position where the “Animal List” value would be in the “Article Title” in the “Find” In this case, the value of interest can be anywhere in the article title, so “Any part of the field” is selected. Optionally, the “Case sensitive” checkbox can be enabled if you want the find operation to be case-sensitive.

 

6.) Select “Append Field(s) to Record:” to populate the column (“Animal List”) containing the extracted value.

 

7.) Run the Alteryx workflow to execute this operation. The end product is displayed below:

 

The “Find Replace” tool in Alteryx provides a convenient way to search for specific values and replace them/produce a new column. Hope this helps in your data preparation task!

The Data School
Author: The Data School