Tableau Desktop and Web Authoring Help

Note: If your join is mis-counting data, it could be a sign you should be using a relationships instead. For more information about relationships, see Relate Your Data. For information about how Tableau handles combining data from different levels of detail (such as many-to-many relationships), see Cardinality and Referential Integrity.

Over-counting values or duplicated data

When you connect to and join multiple tables together, you set up a denormalized version of the data. In some cases, Tableau treats the multiple tables as one table. When multiple tables are treated as one table, after the tables are joined, all tables are queried. This can cause values to be over-counted.

For example, suppose you have two tables. The first table contains employee information, such as employee ID and salary. The second table contains organizational information, such as department names.

Table 1 Employee ID Name Salary 20106 Kim, Michelle 63,000 20107 Wilson, Henry 61,000 20108 Smith, John 65,000

Table 2 Department Hire Date Employee ID Organization Development 6/16/2016 20289 Strategy Support 7/28/2015 20107 Operations Support 8/05/2016 20299 Operations Sales 8/25/2016 20107 Operations

If you join these tables on Employee ID, an employee's salary is counted more than once because the employee is associated with multiple departments.

Employee ID	Name	Salary	Department	Hire Date	Organization
20107	Wilson, Henry	61,000	Support	7/28/2015	Operations
20107	Wilson, Henry	61,000	Sales	8/25/2016	Operations

This is an example of one-to-many cardinality (one employee ID in Table 1 has multiple results in Table 2). For more information on cardinality between tables, see Optimize Relationship Queries Using Performance Options.

To help resolve this issue, you can do one of the following:

• Relate the data: Instead of creating a join, which might duplicate your data, you can use relationships. For more information, see Relate Your Data.

  Change the aggregation: Depending on your analysis, you can use the MIN or AVG aggregation to remove over-counting.

  For example, if you change the aggregation of Salary from SUM to MIN or from SUM to AVG.

• Create a calculated field that removes the duplicate values: You can create a calculation that divides the sum of the field being duplicated by the number of instances of the field that is causing the duplication.

  For example, the Salary values are being duplicated by the number of instances of Employee ID for Wilson, Henry. In this case, in the view replace Salary with a calculated field that uses the following formula: SUM ([Salary])/ COUNT ([Employee ID]).

  You could also use a Level of Detail Expression to delete the duplicate data. For more information, see Removing Duplicate Data with LOD Calculations(Link opens in a new window) in the Tableau Knowledge Base.

• Use custom SQL: Tableau supports using custom SQL for connecting to multiple tables in a data source. You can pre-aggregate the tables before joining them with a GROUP BY clause. This option requires some knowledge about writing SQL queries and assistance from a database expert if possible. For more information about how to connect to a custom SQL query from Tableau, see Connect to a Custom SQL Query.

Broken table joins and cross-database joins

Before you join tables (from the same data source or using a cross-database join), make sure that the data types of the join keys match. If the data types of the join keys don't match, the join breaks, which is indicated by a red exclamation point and the error text "type mismatch between join fields". To fix a broken join, use one of the following suggestions:

• You can change the format of a field (including its data type) in the join dialog to make the join keys match by creating a join calculation. A join calculation supports a subset of calculations that lets you modify the format of the join key in one or more tables that you want to combine. To create a join calculation, click the join icon between the tables that have a broken join, click the field whose format needs to be modified, and then select Create Join Calculation. For more information, see Troubleshoot Joins.

• For text or Excel-based data, modify the data type of one of the text or Excel fields in the join key using the data type menu in the data grid.

• Tableau Desktop only: For most connections, you can use the Convert to custom SQL option to change the data type of one of the fields in the join key, using a function like CAST (). The Convert to custom SQL option is available only when the data source contains only one connection. In this case, remove the second connection and then select Data > Convert to custom SQL.