Tableau Data Relationship

A data relationship in Tableau is a flexible way of combining data from multiple tables or sheets for analysis in tableau.

A relational database (Which contains digital information) or Excel file consists of multiple Tables and sheets.

The multiple tables and sheets are connected to each other in Tableau using the data relationship concept. This connection can be established by Join or Union features present in Tableau. The relationship between data in two or more tables has to be specified while joining tables.

In this article you are going to learn the following things :

  • Join: Combining data from two or more tables to a single table by using joining conditions.
  • Union: Union is a method for combining data by appending rows of one table onto another table.

dashboards-tableau

Join operation in Tableau

Combining data from two or more tables to a single table by using joining conditions. The joining operation is very common in data analysis, you may need to join the data from multiple sources, the Tableau provides the join feature to join tables present in the tableau. You can join up to 32 tables present in the data source.

While joining, the relationship between two or more tables can be specified. The tables present in the data source can be related to each other using the joins such as inner join, left join, right join, and outer join.

The functionalities of Join types are explained below :

Inner Join:

  • It will join all the common records between the two tables or worksheets.
  • The joining condition will be given based on the primary key( The PRIMARY KEY constraint uniquely identifies each record in a table)
  • One or more joining conditions can be specified to join the tables.
  • Many tables can be joined together in Tableau for visualization.

Example: Consider there are two tables TableA and TableB containing customer details and another one is containing Employee details as below.

TableA

Customer Gender Age
Adam Male 24
Jhon Male 32
Jack Male 29
Nick Male 37
Susan Female 31

TableB

Employee Title Wage
Jack Clerk 17$/hr
Jhony Clerk 19$/hr
Mary Mngr 20$/hr
Susan Mngr 19$/hr
  • If you compare two tables(TableA and TableB), you can see that Jack and Susan are both at the customer table as well as the employee table.
  • The Inner join selects the common names from two tables and intersects each other and the rest of the rows are discarded.
  • The resultant table will look like as below.
Customer Gender Age Employee Title Wage
Jack Male 29 Jack Clerk 17$/hr
Susan Female 31 Susan Mngr 19$/hr

The Ven diagram for inner join:
inner-joining-operation-tableau

Left Join :

  • This feature is used to join all the records from the left table and common records from the right table.
  • One or more join conditions can be specified to left join two different tables.
  • Consider TableA and TableB as shown above.
  • Where TableA becomes a primary table and TableB becomes a secondary table.
  • TableA is the primary table, so we cannot discard any rows from this table.
  • The Jack and Susan from TableB will match with TableA and so, the rest of the rows from TableB will be discarded.
  • The resultant table is as shown below.
Customer Gender Age Employee Title Wage
Adam Male 24
Jhon Male 32
Jack Male 29 Jack Clerk 17$/hr
Nick Male 37
Susan Female 31 Susan Mngr 19$/hr

The Ven Diagram for Left join:
left-join-operation-tabaleau

Right Join :

  • This feature is used to join all the records from the right table and common records from the left table.
  • Based on the requirements, one or more joining conditions can be set.
  • This operation is similar to a left join operation, but here TableB will be considered as a primary table and TableA will be considered as a secondary table.
  • The common names from TableA will be added to TableB and the remaining rows from TableA will be discarded.
  • The resultant table will look like as below.
Customer Gender Age Employee Title Wage
Jack Male 29 Jack Clerk 17$/hr
Jhony Clerk 19$/hr
Mary Mgr 21$/hr
Susan

Female 31 Susan Mgr 19$hr

The Ven Diagram for Right join:
right-outer-join-operation-tableau

Outer Join :

  • An Outer join is used to join all the records from both the left and right table.
  • One or more joining conditions can be set to join common records.
  • If you consider TableA and TableB then the resultant table will contain both rows from TableA and TableB.
Customer Gender Age Employee Title Wage
Adam Male 24
Jhon Male 32
Jack Male 29 Jack Clerk 17$/hr
Nick Male 37
Susan Female 31 Susan Mgr 19$/hr
Jhony Clerk 21$/hr
Mary Mgr 19$/hr

The Ven Diagram for Outer join:
outer-join-tableau

Tableau Worksheets

Join feature with Tableau

  • Connect an excel file(P1-SuperstoreUS-15)to Tableau by clicking on the Microsoft Excel option.
    selectexcel-file-tableau
  • Once an excel file is connected, drag the required sheet(Orders Field) into the data window.
    drag-orders-sheet-workspace-tableauu
  • You can connect multiple sheets by dragging the sheets into the data window.
  • Here, I am dragging the Users field to join with Orders.
  • As soon as you drag the Users field, the Edit Relationship dialog box will appear, and it will automatically show you the common field name, here it is given Region as a common field as shown in the below image.
    edit-relationship-tableau
  • Next, double click on the Orders table and add one more table and decide the type of joining.
  • Here I am adding the Returns table to combine with the Orders table.
  • As soon as you add the Return table with the Order table, it will display the join types and common files in both tables as below.
    joining-two-tables-common-filed-tableau
  • Now you can see that the Orders tables have been made of two tables(Order table+Returns table) and the Users table has been made of one table.
  • Like this, You can edit the relationship by selecting the required joining Condition.
  • You can add one or more data relationships between the sheets or tables.

Introduction to Tableau

Union Operation in Tableau

The union is a method for combining data by appending rows of one table onto another table. For example, you might want to add new transactions in one table to a list of past transactions in another table.

Make sure the tables you union have the same number of fields, the same field names, and the fields are the same data type.

In most of the cases, tables with the same headers are appending together using the union function. To perform union operation on two or more tables we don't need any joining conditions.

The procedure to union tables is given as follows:

  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore.xls excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • The Sample Superstore excel file contains the below details.
    sample-superstore-excel-file-tableau
  • Right-click on the Orders Sheet and select the Convert to Union option.
    right-click-and-select-convert-to-union
  • Next, in the data union window,
    • Drag another sheet that you want to union with Orders and then click OK.
      drag-users-sheet-data-window-tableau
  • Now, you can see that the Orders table is made of two tables.
    orders-with-two-tables-tableau

We have learned data relationship with Tableau, Now let us learn Data Sorting and Data Source Replacing in Tableau with respect to data relationship.

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions