Oz du Soleil demonstrates the Right Outer join in Get & Transform to merge two data sets. Oz shows both the why and the how.
- [Instructor] Let's take on the right outer join. You and I are hosting a new product orientation, we've got these two sets of data and there's this question now. Of the people who are enrolled, what departments are they in? Okay looking at our data set we do know who's enrolled and we have their department information in another set, so if that's what we want to do is retrieve the department information into the enrolled data set and we're going to do that with the right outer join.
Okay we have to create our queries with the cursor in the enrolled data set. We go to Data, From Table. Here's our data, I'm going to close and load it as a connection only because I don't want to repeat data that we already have. Close and load to, only create connection, load. And we see in the workbook queries pane, enrolled is loaded as a connection only.
Now let's make our other query. Cursor in that data set, From Table. Close and load to, only connection. Now we're ready to merge our data. New query, combine queries, merge. Let's go down and select our right outer join. And notice here's a top and a bottom as opposed to a left and a right like you might think of a Venn diagram.
Our top is going to be our left side, the bottom the right side. Select this, we want required up top, enrolled on bottom. We want to match up the name fields, okay. Now notice in our query editor this does look like a left and a right, the name and department on the left, new column on the right, let's expand that because that's our right side data. We don't want to use original column name as prefix, okay.
Now let's close and load this to the workbook. Let's look at the data closer. Remember our question was, of the people who are enrolled, what departments are they in? And this is exactly what we have. We can see now that Lloyd is signed up for Wednesday and he is in customer service and we were able to dig this out because we used the right 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