This video show you how to link Tableau tables even though the common fields in the original sources don't have the same name.
- [Instructor] In the previous movie, I showed you how joins between tables that have a common field. In that example, the common field have the same name in each table. In this movie, I will show you how to create a join between tables where the common field has a different name in each table. I'll start in Excel, and my sample workbook is JoinedExamples.xlsx. You can find that in the chapter two folder of your exercise files collection. This workbook contains three worksheets. The first is Customers, where we have customer information.
The index field, or the key field that provides a unique value for each item in this table is customer number, which I've abbreviated as CustNum. Then we also have Orders, and there we have an order number field and a customer number field as well. And note that the customer number field here has the same name, CustNum, as was the case in the customers field. Also there is a ProductID field, which contains the stock keeping unit, or SKU, for the item that was ordered.
And then we'll go to the Products worksheet where we can see, as you might expect, a list of all the products. And here we have the SKU for each of the products. And note that in the Products table, we have SKU as the field name, whereas, for Orders, it is ProductID. So our goal is to join all three of these fields together, the first two based on customer number, and the second two based on product ID and SKU, which are really the same field, just with different names.
So I will now close this workbook and go over to Tableau. I'm in Tableau at the start screen. I want to create a connection to my Excel file, so I'll go over to the connect portion of the navigation pane and click Microsoft Excel. Then in chapter two, I will double-click JoinExamples. And here I see my three sheets, Customers, Orders, and Products. I will double-click customers to add it first. There we go. Then I will double-click orders to add it.
Note that Tableau is able to identify that there was a common field between these two tables. And if I hover my mouse pointer over the join indicator here, it says that there's an inner join and that's based on a field where one is the primary key of one table versus a foreign key or an informational field, if you would, for another. And if I hover back over, it shows that the common field is customer number in both of those tables.
Now I can double-click products. And what happens is that Tableau is unable to identify a common field. So we need to tell it. We see the join dialog because Tableau was unable to identify the two common fields, one in Orders and one in Products. So we need to help it by identifying them. So we'll go down the list of fields and we're looking for Product ID, so I'll click that. And then we see Products here on the right. That is the field we want to use. And I will hover over the cell right below the product's header, click the down arrow, and we want to create the connection between Product ID and SKU.
So I will click SKU. And we get our combination and I can close the join dialog box. So now you see we have all three tables combined together. And if I click sheet one to go into the body of the workbook, then I see all of my data from the Customers, Orders, and Products tables. So if I want to put in a customer full name to the Rows area, I can do that here by dropping it over. And then I can add Product Category as a second level of organization on the Rows shelf.
There we go. And then I can add the number of orders for each of those product categories. So I will scroll down under Measures and drag number of records to the data area. And there you can see that Adara Langley had one battery order, one grid tie inverter order, two for solar panels, one for wind harvesters, and you have similar data for all the other customers who have placed orders. So as you can see, bringing data together from multiple tables, even if the common fields had different names, can allow for some very powerful insights.
- Managing data sources and visualizations
- Managing Tableau worksheets and workbooks
- Creating custom calculations and fields
- Analyzing data using statistical tools
- Sorting and filtering Tableau data
- Defining groups and sets
- Creating and pivoting crosstabs
- Formatting Tableau visualizations
- Creating basic charts
- Annotating and formatting charts
- Mapping geographic data
- Creating dashboards and actions
Skill Level Beginner
Creating Interactive Dashboards in Tableau 10with Nate Makdad2h 24m Intermediate
Integrating Tableau and R for Data Sciencewith Ben Sullins1h 10m Intermediate
1. Introducing Tableau
2. Manage Data Sources and Visualizations
3. Managing Tableau Worksheets and Workbooks
4. Creating Custom Calculations and Fields
5. Analyzing Data Using Statistical Tools
6. Sorting and Filtering Tableau Data
7. Defining Groups and Sets
8. Creating and Pivoting Crosstabs
9. Formatting Tableau Visualizations
10. Creating Basic Charts
11. Annotating and Formatting Charts
12. Mapping Geographic Data
13. Creating Dashboards and Actions
Next steps1m 7s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.