Joins in Tableau
Various relational data sources are composed of a collection of tables that are connected by particular fields. Such as, tableau join data sources for a Company may have a table for an employee that contains the first name, last name, phone number, etc. of employees.
In addition, there may be other tables for titles that have the price, royalty, and title of published books. In order to examine these two Tableau join tables collectively, to answer questions as, how much was paid in royalties last year for a particular employee, you would join the two Tableau join tables using a common field such as Employee ID. That way you can view and use the fields from both tables in your analysis.
Tableau Joins
When you first connect to a tableau join data source using the connection dialog box then you can connect to several tables. Here you can add tables, specify Tableau joins, and modify the field aliases in the case you have likely named fields in each of the tables.
If you have already connected to the data source after that you can add tables also. This section explains how to connect to several tables as well as add tables to the Data window.
Check out this insightful video on Tableau Tutorial
Connecting to Multiple Tables in Tableau
- Firstly, view the table or select the table you want to start with (typically the fact table) and then select the Multiple Tables option.
- Click the Add New Table button at the bottom of the dialog box.
- In the Add Table dialog box select a table to add to the Data window.
Optionally change Field aliases.
At the bottom of the Add Table dialog box, there is a catalog of fields with their aliases. Double click the field alias to alter how the field will be presented in the Data window. This is useful when you have copied field names across tables or your field names are not very clear.
Add a join by selecting the Join tab.
Tableau will automatically build a join for you based on the formation of your data. Select the Joins tab to examine the join clause to make sure it is how you want to connect the tables.
You can delete useless join clauses by choosing them in the catalog of join clauses and clicking Delete.
By choosing a field from the original table, a field from the added table, and an operator you can add one or more join clauses in the join pane. After that Press Add button to add it to the list of join clauses.
According to the earlier discussion data source has two tables order information and user’s information, we can join both tables based on the region field. Select Region in both lists of fields, select equal sign as an operator and then click Add.
You can select Inner, Left, or Right join from the Join Type drop-down list.
Note, you can’t nest Inner joins inside Left or Right joins. Because these joins will cause a join expression not supported error.
When finished, click OK.
In connection dialog box tables are listed with the foreign keys.
End the connection by giving it a name and clicking OK.
When we add Tableau join tables, Group by table command is used when the data window is automatically organized. You can turn off this feature or change how the Data window is sorted using the Data window menu.
Adding Tables to the Data window in Tableau
- Select a data source on the Data menu and then select Edit Tables.
- In the Tables dialog box, click Add New Table.
- In the Add Tables dialog box, select a table to add. Complete the dialog box by defining at least one tableau data joins clause and optionally changing field aliases.
- When finished, click OK and then OK again to close the dialog boxes.
- When you connect to several tables you are necessarily connecting to a denormalized view of the data source. This means that all queries are run against all tables and it is possible for some measures to be overcounted.
Editing Tables
By using the Tables command you can modify the tableau data joins tables. If you want to edit a table to add or remove fields from the Data window, modify the join clause, or add more tableau join clauses to further define how the table is connected to the original table.
Select a data source on the Data menu and then select Edit Tables.
In the Tables dialog box, select the joined table you want to modify and then click Edit.
You can alter the table and field aliases as well as add and remove fields from the Data window in the succeeding dialog box. Choose the Join tab to edit the join clauses.
When finished click OK twice to close the Tables dialog boxes.