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 "CostumesByState" and "StateFile". 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 Jellybeans datafile. Using the Input Data tool, bring the CostumeByState, StateFile and Jellybeans 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 the results, some rows with NULL values are seen. Use the Data Cleansing tool to remove these values from the dataset. Locate the Data Cleansing tool in the tool bar, drag it down to the workspace and connect it to the Join Mulitple tool.
Click on the Data Cleansing tool. Under options > Remove Null Data, select Remove null rows. Under Replace Nulls, select Replace with blanks. Now click on RUN.
NULL values have now been removed from results.
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 Jellybean datafile and the StateFile datafile onto the workspace, Drag the Find Replace tool onto the workspace and connect the Jellybean datafile to the F anchor and the StateFile 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.
