on
10-24-2017
11:28 AM
- edited on
04-29-2024
01:18 PM
by
olacey
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.
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.
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.
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 -
Good writeup...however I am confused by the first paragraph. It states "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 B rows with C plus D columns. ".
Should the first paragraph be: "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."? I confirmed by appending a 3x4 input table with with a 2x2 table and it returned a 6x6 table.
Can you please clarify?
Thanks!
Atom,
You are correct regarding the typo in the first paragraph. It should state: ... A times C rows with B plus D columns.
See,This will help
A Cartesian join, also known as a Cartesian product,
is a join of every row of one table to every row of another table.
For example, if table A has 100 rows and is joined with table B, which has 1,000 rows, a Cartesian join will result in 100,000 rows. (A x B)
Thanks for the show cases, made it easier to utilize the tool.
I have a silly question regarding the cosmetics of the Designer. How can we rotate the tool icon such way connections can be pointing down? it sure make the workflow much cleaner.
Regards
AliAS
This is the first time i've come across a 'wireless' connection: So elegant!
Your file is not working. Could you please double check and upload another one?
Something to be aware of, and which I was surprised was not mentioned in the write-up:
When you append records to an empty data stream Alteryx seem to by-default auto-create a record to add the data to. So if you have zero records in your source data and you append data to this data stream, Alteryx will generate records not originally in the data stream in order to add the appended variables in. The values of the variables in the original data stream will be null, but records will exist which did not exist before the append tool was reached. Alteryx will give you a warning, but the append is allowed.
This behavior can cause issues in batch macros because of the unpredictable nature of the output of the tool and can also lead to undesirable output formats in other settings, e.g. in the form of 'output of nulls' - which should not have been output, because these are 'ghost records' and no actual records existed in the data set.
BYJE
I have had same kind of issues with null values.
If you would like to append some information to original source of append tool only when there are values present, you need to link source data to T anchor instead of S. Alteryx will consider the appended information as a source and won't create null values if there are no rows in original data source.
To those who couldn't opened the file, I went to the folder where the file was saved and right clicked and selected "edit", and it allowed me to open the file in Designer. Hope this helps!
@AliAS2020 , you can rotate the workflow on the canvas by using the Layout Direction in the configuration pane by selecting Vertical.
Thanks.
@patrick_digan , I downloaded the Append.yxwz file but it didn'r worked and do not have below program into the package.
Thank you both for the heads up on the typo! It should be fixed now.
Whoa !!!
Good writeup
FYI, if you use the Dark Mode, you won't be able to see the download symbol... You should switch to Light Mode.
In first paragraph , I am unable to understand firstly but after reading this for 2nd time the issue had removed
@patrick_digan , I downloaded the Append.yxwz file but it didn'r worked and do not have below program into the package.
Hello,
Thank you @patrick_digan for the lesson. The attached Append.yxwz can't open, keeps saying no data.
informative.
Thanks for the show cases, made it easier to utilize the tool.