Sign in or start a free trial to avail of this feature.
5. Appending Data
In this lesson, you will learn how to use the Appending Data tool to bring together data that does not necessarily share a common field.
- The Append Fields tool allows users to merge datasets that do not have any common fields
- The Append Fields tool can only accept two inputs – a Target and a Source
- Data from the Source dataset will be brought onto the Target dataset
In previous lessons, we've discussed joining data where two distinct data sets have at least one common field.
What if two data sets do not share a common field but you'd like to bring them together anyway? For this, we can use the append fields tool.
Appending two data sets can be useful when you're trying to establish a complete data set with all the relevant data in one place.
Let's use an example to explain this more clearly.
We'll start by importing the customer data set and the incentive plan data set onto our canvas.
The customer data set contains the name of each of our customers together with the contact person.
The incentive plan data set contains a list of our sales representatives together with some commission data.
Let's say that the 6 sales representatives are free to speak to any customer.
Therefore, a combined file needs to list each of the 6 sales representatives beside each company.
We can accomplish this with the append fields tool.
We'll navigate to the join tab on the tools palette and bring the append fields tool onto the canvas.
Notice that the top input node is labeled T for target while the bottom input node is labeled S for source.
The target node should be connected to the data set that you want to append, while the source data set should contain the data that you want to add to that target.
As such, the source data set should contain fewer records than the target data set.
We'll connect the customer data set to the target input node and the incentive plan data set to the source input node.
This will allow us to bring the sales rep fields from the incentive plan data set to the customer data set.
We'll now click on the append fields icon to bring up the configuration window.
From here, we can reorder and deselect fields just as we would with the select tool.
I will deselect the unknown field since we don't need it going forward.
We'll now right click on the append fields icon, select add browse after and run the tool.
In the output window at the bottom of the page, we can see that we have 480 records.
This is because we have combined 80 customers with 6 sales reps.
This is something to keep in mind when appending data.
The number of rows you are working with can explode.
As we take a closer look at the data, we can see that each company is now listed besides each sales rep allowing us to carry this sales rep commission information forward with respect to each company.
Let's combine our sales rep commission data with monthly sales data.
We'll bring sales data onto the canvas, join the appended data to the sales data by both customer ID and sales rep name and run the workflow again.
When we click on the J output node, we can see that each row of sales data now contains the company contact name information together with the sales rep commission threshold information.
Over the past several lessons we've looked at how the join tools can help with the most common data merging scenarios.
We have looked at how do merge data sets that have similar structures merge based on a common field and even when data sets don't have any fields in common.
Now that we have covered basic techniques for merging and preparing data, we'll look at more advanced data manipulation options in the next course.