Overview
For those familiar with VLookup in Excel, the Find Replace tool in Alteryx works similarly in that it can replace known values in a dataset. The Find Replace tool can also be used to append additional data from a lookup table based on the common field.
The Find Replace tool is made up of 2 input anchors and 1 output anchor.
◆ The F (Find) input anchor connects to the main dataset that has the column whose values we want to replace or match with other values
◆ The R (Replace) input anchor connects to the lookup table that contains the values we are searching for and contains values that we want to append
◆ The output that comes out from the output anchor is the main dataset connected to the F input anchor with the changes configured through the Find Replace tool
Configuration
Once the data has been connected appropriately to the F (Find) and R (Replace) anchors, we can move on to the configuration window:
By default, the Find Replace is Case Sensitive and can match values based on the choices: beginning of field, any part of field, and entire field. However, by choosing Match Whole Word Only makes sure that Alteryx doesn’t match values that are part of a string but rather the whole word. If that’s confusing now, don’t worry! There will be some examples below better explaining the options in the Configuration window.
In the section under Find, we see that there are three options to determine how to match the values from the dataset to the values from the lookup table. The dataset and lookup table I’ll be using to explain the options in the configuration pane is as follows:
Beginning of Field: The Find Replace tool will search the columns for the lookup string at the beginning of the string value
For example, let’s say we want to replace any value with an N, W, S, E in the beginning of the address with the corresponding value from the lookup table, what would the output look like?
Note that only records 5-7 have been affected!
◆ Wish Drive becomes Westish Drive
◆ Neaster Road becomes Northeaster Road
◆ Sconnie Avenue becomes Southconnie Avenue
Any Part of Field: The Find Replace tool searches the column for lookup value anywhere in the cell
For example, using the same dataset and lookup table from above, the results will look like this:
Note the differences in the results between finding matching values at beginning of field and finding matching values at any part of the field:
◆ Records 1-4 are affected as are records 5-7, similar to before
◆ Notice how in record 3 “9 S Street” becomes 9 South Southreet! This is because the Find Replace tool was configured to find the ‘S’ within the cell of which there are two instances. Despite Southtreet not making sense to us because we know that’s not what we were looking to replace, the configuration triggered Alteryx to recognize it as a match
To make it so that Street doesn’t become Southtreet, we can check off “Match Whole Word Only” so only values matching the lookup value exactly are replaced:
Another way to make sure that Street does not become Southtreet is by unchecking the box “Replace Multiple Found Items (Find Any Part of Field only). By doing so, Alteryx will only replace the first match with the value from the lookup table:
Entire Field: The Find Replace tool searches the column for the lookup value to match exactly to the value in the cell.
If the string in the cell does not match the lookup value exactly, no changes will be made:
For example, if we add an additional record as follows and run the workflow again, we can see the change is only made to row 10 because the lookup value and the string within the cell match exactly:
In the next section, Replace, we can see that there are two main options:
◆ Replace Found Text With Value
◆ Append Field(s) to Record
Replacing Found Text With Value allows us to specify the field from the lookup table/the dataset connected to the R input anchor to use to replace the field in the original dataset/the dataset connected to the F input anchor. As mentioned above, by clicking on the Replace Multiple Found Items (Find Any Part of Field only), it will replace all matches. Clicking off the Replace Found Items option will replace the first match in the cell.
Appending Field(s) to Record will list all fields in the lookup table that we can choose to append to the dataset. In this case, it would be the Lookup Value column and Abbreviation column.
Hope that this blog helps you better understand the Alteryx tools! If there are any topics you would like for me to cover or if you have any questions, please feel free to reach out to me via LinkedIn: https://www.linkedin.com/in/jessicaknkwan/. Until next time, let's continue to analyze away!