Oz du Soleil demonstrates the Full Outer join in Get & Transform to merge two data sets. Oz shows both the why and the how.
- [Instructor] I'm going to show you a full outer join in Get & Transform. You and I are hosting a new product orientation and we have these two lists. The people who are required to attend and their departments and the people who are enrolled and their session. Our goal is to create a master list, bring everybody together, including their department, session and where there's a match on the left side and the right side we want those all on one row. Here's the full outer join.
Both data sets are already in tables, so let's make our queries. With the cursor in the data set, the data, From Table. And here is our list of people who are required and I'm going to close and load this as a connection only because if I load it to the workbook it's just going to be the same information that we already have. Okay go to this arrow, close and load to. Only create connection, load it.
Okay? And notice on the right side in the workbook queries panel this is loaded as a connection only. And let's do that on the enrolled side. The cursor's in a data set, From Table. Close and load to, only create connection. Now we can merge our queries. New query, combine queries, merge. Thinking about the Venn diagram, the left and the right.
This interface, the top is the left side, the bottom is the right side. Let's put the required list on the top. And we have enrolled and we're going to match up the names columns. Name, name. Select our full outer join. Here's our data and again it looks left side right side.
The left side is already expanded name and department, right side is collapsed. We have to expand it and we do want all of these columns, we don't want to use original name as prefix. Unselect that, okay, there's our data. But let's close and load it to the workbook. This is the data we want to see and we will see it clearly this way. Now remember our goal was to bring everybody together into one list, whether they had a match or not, here is the master list that we created with the help of full outer join.
And we can see Nisha is customer service and signed up for the Wednesday session. Now you will notice that the names are in two different columns and we can easily clean that up but for right now we just got to appreciate the full outer join.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins