Tool Mastery
Explore a diverse compilation of articles that take an in-depth look at Designer tools.Tool Mastery | Append Fields
This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Append Fields Tool on our way to mastering the Alteryx Designer:
The Append Fields tool adds every row of the source input to every row of the target input as new columns. This is known as a Cartesian or cross join. If your target input has A rows with B columns and your source input has C rows with D columns, the Append Fields tool with output A times C rows with B plus D columns.
- Perform a Cartesian Join (attached in the workflow Append.yxwz):
There are times when you want to join every row from one table to every row of another table. Let’s suppose you have a list of possible customers and your product inventory. You could use an Append Fields tool to join this data:
I started with 26 products and 26 customers, which yielded 26 x 26 = 676 possible combinations of customers and products. Since the products table is going into the Target (T) input, my output is sorted by that table. If we were to switch it and have the customers be the target input, the only difference would be the order of the columns (Customer would be the first column) and rows (Customer1 would occupy the first 26 rows as each Product was listed sequentially). Since there were more than 16 records in my Source table, I had to change the setting on the append tool to allow for this:
This option is in place because Cartesian joins are problematic if you accidentally try to append too many records.
- Append Single Record to Entire Dataset(attached in the workflow Append.yxwz):
Sometimes you may want to tack on one piece of info to every record. This is a great time to bust out an Append Fields tool. Suppose you have a dataset full of color names (with possible repeats). In a master table, you have each color listed along with a value. Your goal is to let your user pick a given color, and return all the records from your dataset with values less than or equal to the selected value.
Step 1 - Grab the user selected color and find its value from our master table:
The user selected Blue, so we have looked into the master table to find that is a value of 10.
Step 2 – Add the Values to our dataset of colors:
Since our dataset just has colors, we can use a Find Replace tool (or a Join if you want it to waste a nanosecond sorting the data J) to add the value from the master table. Every record has a value now, so far so good.
Step 3 – Append the user selected value and apply a Filter to only output records with a value less than or equal to that:
Note that we setup our append tool to change the Value from the Source side to be Max_Value as well as deselect the color from the Source side:
The Append Fields tool has all of the same options and abilities like the Join tool to change the data.
- Append Current DateTime to all Records(attached in the workflow Append.yxwz):
Let’s suppose that you want every record in your dataset to have a timestamp when it’s output. The catch is that you want every record to have the same timestamp from when the workflow began. That eliminates using a Formula tool which would recalculate it for every record so it could end up with timestamps that are a few seconds off. One simple way to accomplish this task would be to use an Append Fields tool. We’ll use a Generate Rows tool to get the timestamp and then append it to all of our data streams:
I’ve changed my source connections to be wireless to make it look cleaner:
That’s it! An Append Fields tool is basically a join tool with no joining conditions and just a J output. Check out the Select tool’s help doc for more option on the “options” for the Append Fields tool.
By now, you should have expert-level proficiency with the Append Fields Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.
Stay tuned with our latest posts every #ToolTuesday by following @alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.
Additional Information
Click on the corresponding language link below to access this article in another language -
@AliAS2020 , you can rotate the workflow on the canvas by using the Layout Direction in the configuration pane by selecting Vertical.
Thanks.
