Alteryx Joins vs. Find Replace - 2 Ways to Connect Data

by Ben Connor

Part 1: Joins

There are several ways in alteryx to join sets of data together which can be invaluable when faced with multiple data streams. The most common tool used to connect data sources with 1 or more matching field types is, unsurprisingly, the join tool.

This allows us to specify the field types that match between our data and match the rows from each source together based on those values. For example, in the image below I am able to join 2 sets of data (one containing mock sales data and the other containing country names) together as they both contain a field for the 3 letter country code e.g. AUS for Austria

After joining you will have a single data source containing the new fields that you have brought through from your second data source (in this case 'Country').

The advantage of using the join tool is that it is easy to edit and deselect fields using the embedded select pane within the tool itself. This allows for quick and easy manipulation of field names, data types and which fields are selected to be brought forward into the rest of the workflow. For example here I would deselect the 'Right_Country Code' field as this is duplicated from the join.

I have then successfully joined by two data sources giving me the country names in an additional field.

Part 2: Find Replace

There is however another option for joining data sources by matching field in the ‘Find Replace’ tool.

This can sometimes be a simpler and more efficient way of joining two sources. It works by connecting the data source in which you want to 'find' values to the top 'F' connection (the country sales data in this case) and the data source that you want to 'replace' things with into the bottom 'R' connection.

To configure this tool, in the 'Find' section, you have to select which field you want to look in (Country code) and which field you want to look for from the second source so that you can either replace or join it based on that value (again country code as this is what we are joining on - similar to joining on country code in the join tool).

Once this is configured we can move to the 'Replace' section where we define what we want to do (Append or Replace).

In this case, we want to just add a new column with the country name to our original data source and so we can select 'Append Fields to Record' and select the field we want to append i.e. Country.

This will append a new field with the country names onto the original data source - matched up with the country codes, giving you the same result as if you had used a join tool. If you only wanted the country name instead of the country code then you could use the 'Replace' option instead.

One of the main benefits of this is that it cant be slightly quicker to configure and a join tool cannot edit an existing field wheareas find replace can. The downsides are that it doesn't have the same embedded select functionality as the join and you can't join on multiple fields at the same time. It is also easier to see if any records could not be joined for whatever reason in the join tool (e.g. no matching values).

Both are valid ways of connecting data sources together but each has its benefits depending on the situation!