How to Create Joins and Unions with Tableau’s Physical Layer

Learn about Joins, Unions, and Tableau’s Physical Layer

This video demonstrates how to access Tableau’s Physical Layer and combine tables using traditional Joins and Unions instead of Tableau’s default Relationships.

Hi, I am Felicia Styer with Playfair Data, and in this video, I’m going to show you how to create joins and unions inside of Tableau’s physical layer. I’ve set up this workbook to use the Orders table from Tableau Superstore, and I’m going to be adding joins to the People and Returns table in the physical layer along with a union to another orders table. So, my first step is going to be accessing Tableau’s physical layer by double-clicking on the Orders Logical table.

All right, now we’re in the physical layer. Each logical table in your logical layer will have its own physical layer. So, like a relationship, if I drag People in and drop it, Tableau will automatically generate a join. I can click on that Join icon to change the join type between inner left, outer right, and then also change our join conditions. Tableau has automatically recognized that both tables have a region field and has set up this join for us. We don’t need to change anything, it’s good to go.

Now, I’m going to add the Returns table as well, and let me show you that it matters where I drop this. So if I were to drop it directly on top of Orders, it’s going to replace that table instead. We don’t wanna do that, so I’m going to undo it. If I drop Returns directly below Orders, I get a union instead of a join. But I want a join, so I’m going to drop it anywhere else in the canvas. All right, now just like Orders and People, Orders and Return share a field and Tableau’s automatically recognized and set up this join condition for us, and we’re good to go.

Moving into something slightly more complicated, let’s say I have some historical orders data that I’d like to union along with the Orders table. Now, if I need to add in data from a second connection, I can go to this Connections pane and add a file. Now this can be a text file, a database connection, I’m not limited to just Excel, but here I’m also using an Excel file. And I’m going to pull in this Superstore Archive file that I created with Mock Data. After adding it, Tableau’s automatically added a sheet from that file into our joins, and I’m going to remove that, because we don’t actually want to join that data in, we wanna union it. Now, if I union a file from our original data connection, I can create a union just by dropping like we demoed before, but this is limited to Tables inside of a data connection, we can’t do this crossing over connections. So if I try to drop Order Archive right here, it won’t allow me to do that. What I need to do instead is convert this table into a union, and this prompts this Orders plus Union menu.

I’m going to change the union type from manual to wildcard, and then I’m going to look for a pattern. So I know both of these sheets include the string order, so I’m going to include worksheets that start with order and ends in any way. And I want to pull these from all workbooks in the folder, so I’m going to replace this with a star for a wildcard search. I don’t need to expand the search to subfolders or parent folders. This is enough, I’m going to hit Okay. And Tableau will take a minute to go and query those files. All right, let’s check to see if that union worked.

I’m going to Sheet 1, and I’m going to double-click on Order Date. And now we can see that this order date extends all the way from 2017 to 2024, so our union was successful. Going back into the data source, Tableau has automatically closed the physical layer. If I’d like to access it again, all I need to do is double-click on the logical layer and then to move from the physical layer back to the logical layer, I can just X out of this physical layer. Like I said before, every single logical table in this canvas will have its own physical layer. So I’m going to add Order Archive here just to demonstrate and show you that we can access its physical layer, and we don’t see any of the joins or unions that we created before. All right, that’s the basics of how to set up joins and unions on Tableau’s physical layer. If you’d like to learn more about the physical layer, logical layer, and joins unions or relationships, please check out my written tutorials about bringing tables together on Playfair’s website. Thanks for watching. This has been Felicia Styer with Playfair Data.