Joining Tables in Tableau
Various relational data sources are composed of a collection of tables that are connected by particular fields. Such as, a data source for a Company may have a table for employee that contain the first name, last name, phone number, etc. of employees.
Get to know more about Performance of The Tableau Server that can help you grow in your career..
In addition, there may be other table for titles which have the price, royalty, and title of published books. In order to examine these two tables collectively, to answer questions as, how much was paid in royalties last year for a particular employee, you would join the two tables using a common field such as Employee ID. That way you can view and use the fields from both tables in your analysis.
When you first connect to a data source using the connection dialog box then you can connect to several tables. Here you can add tables, specify 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 adding tables to the Data window.
Connecting to Multiple Tables
- 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.
In 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 it 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 earlier discussion data source has two tables order information and user’s information, we can join both tables which based on the region field. Select Region in both lists of fields, select equal sign as 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 joined tables, Group by table command is used when 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
- 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 join 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 over counted.
By using Tables command you can modify the joined tables. If you want to edit a table to add or remove fields from the Data window, modify the join clause, or add more 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.
Are you interested in taking the Tableau basic training?