Oz du Soleil demonstrates the Left Anti join in Get & Transform to merge two data sets. Oz shows both the why and the how.
- [Instructor] Let's tackle the left Anti Join. You and I are hosting a new product orientation and we have two lists, lists of people who are required and lists of people who are enrolled. Now we have a question. Who are the people who are required and not enrolled? We can see the Eunice is on the required list and is not enrolled, but we aren't going to go through this whole list and eyeball it, we're going to use the left Anti Join.
Let's do it. Our datasets are already in tables. Let's make our queries. With the cursor inside one dataset, got to Data, From Table. And here is our required data. I'm going to close and load this as a connection only, because if I load it to the workbook, it's going to be the same information that we already have. Hit this arrow, Close & Load To, Only Create Connection, Load.
And notice in the Workbook Queries pane, we have our Required query and it is indeed loaded as a connection only. Now let's make our enrolled query. With the cursor in the dataset, Data tab, From Table. And there is our unadulterated data, so we're going to Close & Load it to a connection only. Let's merge the data using our left Anti Join.
New query, Combine Queries, Merge. And here's our Merge interface. And if we think about the Venn Diagram as left and right sides, in this interface we think about it as a top and a bottom, where the left side is the top, bottom is right. We want Required on the top. Enrolled on the bottom. We're going to match the Name columns. And we're doing the left Anti Join.
OK. Now we see it looks like a left and a right. The left side, the Name and Department is expanded and our right side is collapsed. Let's expand it. We don't want to use original column name as prefix. OK. And notice we only have data in the first two rows. Those are the left side. There were no matches on the right side. So we can get rid of those last two columns. They're already highlighted, so we can go right-click, Remove Columns.
Let's close and load. And look at this a little closer. And here's our result. Remember, we were looking for the people who were required and have not enrolled. And we noticed that Eunice was on that list, but not we also see Ty, Latika, Collette, and Karen need to be contacted as well about hey, when are you going to sign up?
- 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