Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

When to use Join vs Append

AndrewL
Alteryx
Alteryx
Created

Question

I feel like this is a simple answer and I kind of feel like I know the nuances between when to use the tool. I'm finding it hard to articulate in which use cases one is better than the other. Can anyone think of when would best to use append instead of join?

Answer

To use the Jointool, ideally you'll want to have a field in both datasets that match. Either an ID of some sort, or a combination of fields. The Join tool allows you to add data to your dataset only where it matches based on the common field.

There is an option within the Join tool to join on record position. This acts sort of like an append where the first record from each input is matched regardless of the data within it, however it will only match the records to the lowest record count from the Inputs. For example, if one input anchor has 100 records and the other has 1,000, only the first 100 records will Join using this method.

TheAppend Fieldstool adds all of the information from the S input (Source) to the records in the T input (Target). If you're not careful, you can blow out your data into a huge dataset. If you have 100 records on the Source side that you are appending to 1,000 records on the Target side you will end up with 100,000 records because each of the 100 Source records gets added to each of the 1,000 Target records.

To help with this issue, there is a setting at the bottom of the configuration for the Append Fields tool allowing you to decide what to do if the append creates more than 16 records: Error, Warn, Allow All Appends.

No ratings
Comments
Garabujo7
Alteryx
Alteryx

Great answer, is kind of confusing the difference, both look quite similar but the differences are really subtle but relevant

SeanAdams
17 - Castor
17 - Castor

Hi @AndrewL - the way that we teach this is that:

 

- Join tool is when you want to match one set to another like an excel VLookup - the data which is added is different depending on the match.

- Append tool is mostly used in 2 ways:

        - when you want to add a new field to an existing data set - the data that is added is the same for every row.   

        - Multi-row appends act as a cartesian join (which is not something that you can do in the regular old join tool unless you do a bunch of workarounds)

 

The other difference with Join is that joins can not only enrich data - but they can also act as a filter - Append does not have the ability to act as a filter, only adds info.