Oz du Soleil demonstrates the Left Outer join in Get & Transform to merge two data sets. Oz shows both the why and the how.
- [Instructor] Let's take on the Left Outer Join. You and I are hosting a new product orientation and we have two sets of data; the people who are required, the people who are enrolled. And, we have a question. Of the people who are required to attend, what sessions have they signed up for or not signed up for? This is the equivalent of VLOOKUP in Excel where we have one dataset that we want to complete, but complete it with data from another dataset.
And, that's exactly what the Left Outer Join is going to do for us. The datasets are already in tables. Let's make our queries. With my cursor in the dataset; Data, From Table. Here is the required dataset and I'm going to load this as a connection only because loading it to the workbook would just load the same data we already have. The arrow, Close & Load To.
Only Create Connection, Load. Notice in the workbook queries panel, this required query is indeed setup as a connection only. Let's make the enrolled query. The cursor in the dataset. From Table. Close & Load To. Only Create Connection, Load. Now, let's do our merge. New Query, Combine Queries, Merge. The interface defaults to the Left Outer Join and if we think about our Venn diagram with the left and right sides, here we think top bottom.
The top is the left side, the bottom is the right side. We want to put our required data on top, enrolled on bottom, match up the name fields. OK. Notice this looks left side right side. Name, Department, that's the left side. And, it's already expanded. Let's expand the right side. We don't want to use original column as prefix, OK. And, let's close and load this to the workbook.
Let's look at it a little closer. And, here's our data and remember what our goal was. We wanted to take everybody who is required and get the sessions they signed for or not signed up for. And, now we can see Eunice has not signed up and Lloyd is signed up for Wednesday. And we were able to easily do that with the Left 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