Join Tools
Join tool 
The Join tool combines two inputs based on a common field(s) between the two tables (Notice the Join tool has two (2) input anchors). Inputs can also be joined based on record position. For this, records need to be in the same position and have the same fields in both inputs. It is recommended to join inputs based on fields.
To get started, download the datafiles "Valentine Candy" and "PriceRings". Next, open Alteryx and use the Input Data tool to bring the two files into your workspace. Click on the Join link in your toolbar and bring the Join tool onto your workspace (be sure to get Join and NOT Join Multiple). Connect the two data files to the Join tool.
Once you connect files to the Join tool, the configuration box will display options for the Join tool. Use the Join by Specific Fields option to join the two datafiles. Using the dropdown menu, select State as the field to join by. Next, deselect any fields you don't want to see in the results. Since the State field in the Right input is not needed, deselect it.
Now, run your workflow.
In the results, you can see that the table contains information from both datafiles.
Join Multiple tool 
The Join Multiple tool combines two or more inputs based on a common field between the input tables. By default, the tool outputs a full outer join.
Download the StateMaritalStatus datafile. Using the Input Data tool, bring the ValentineCandy, StateMaritalStatus and PriceRings datafiles onto the workspace.
Use the configuration pane to select the State field as the common field to use to join the datafiles.
Deselect the State field for input #2 and Input #3 to prevent repeated values in the data. Once selections are made, click RUN to run the workflow.
In our results, we see that the States are not in alphabetic order. In order to change that, we are going to use the Sort tool. Under Preparation, locate the Sort tool, drag it down to the work space and connect it to the Join Multiple tool.
In the Configuration Pane, select State as the field to sort by, then Ascending for the order. Next, click RUN to run our workflow.
In our results, we see that the states have been sorted in ascending order.
Find Replace tool 
Use the Find Replace tool to replace or append data in a column using data from another data stream. The Append option is similar to an Excel VLOOKUP.
The Find Replace tool has two inputs:
- F anchor: The left input is the initial input table stream "F" - for "Find." This is the table to be updated with the results.
- R anchor: The right input is the lookup table "R" - for "Replace." This is the table containing data used to replace data in (or append data to) the original input.
Using the Input Data tool, bring the PriceRings datafile and the StateAbrv datafile onto the workspace, Drag the Find Replace tool onto the workspace and connect the PriceRings datafile to the F anchor and the StateAbrv. to the F anchor.
The Configuration pane has two sections: the "Find" section which identifies the field containg the values to be found and the "Replace" section which has the replacement data. This section gives the option to "replace" data in a field or "Append" a field to a dataset (adds a new field).
- In the Find section, select Entire field
- In the Find within Field, select State
- For Find value, select State
For the Replace section, select Abbrev. as the "Replace Found Text with value". Click RUN to run the workflow/
In the results, all state names have been replaced with state abbreviations.
Go back to the Replace section. this time select "Append Field(s) to Record" Next, select Abbrev. and Population
Once the selections are made, run the workflow.
The Abbrev. field and the Population field have been added at the end of the spreadsheet.
