Troubleshoot Joins - Tableau

相关文章推荐

英俊的作业本 · 步骤 2：设置群集 - Tableau· 2 周前 ·

苦恼的伤痕 · 使用加速器快速可视化数据 - Tableau· 2 周前 ·

非常酷的柳树 · 配置数据集 - Tableau· 5 天前 ·

乖乖的椅子 · 江苏省人民政府往期回顾 ...· 2 月前 ·

才高八斗的冰棍 · 中际旭创总裁刘圣：坚持创新企业文化，选择产品 ...· 4 月前 ·

谦和的小蝌蚪 · 7.82亿元！百余“辽字号”亮相农交会· 5 月前 ·

微笑的青蛙 · 为什么关于贝恩杯的信息好少？ - 知乎· 9 月前 ·

帅气的生姜 · 2024年中国稀土行业研究报告 - 21经济网· 9 月前 ·

</noscript><div id="app" class="wrapper"><header id="tableau-help-article-header" class="container--full-width quick-help-header"><div class="container--centered"><div class="header__back-button"><back-button title="Go back"/></div><div class="header__mobile-menu quick-help-hidden"><menu-tree-toggle/></div><div class="header__logo quick-help-hidden"><a href="https://www.tableau.com/en-us/"><img src="./Resources/tableau-logo.png" class="header__logo__img" alt="Tableau"/></a></div><div class="header__search"><search-header-help placeholder="Search"/></div><div class="header__home-button"><home-button title="Go home"/></div></div></header><div class="container--navigation-top quick-help-hidden content-only-hidden"><div id="help-subheader" class="subheader print-hidden"><div class="container--centered"><h4 class="heading--subheader">Tableau Desktop and Web Authoring Help</h4></div></div><div class="container--top-links"><div class="container--centered container--breadcrumbs"><div><breadcrumb-links-help/></div></div><div id="help-container-menu-headings" class="container--menu-headings"><nav class="nav-medium-screen"><menu-heading-links-static-help menu-title="In this article" :disabled="false" :headings="pageHeadings"/></nav></div></div></div><div class="section--main container--full-width"><div class="container--centered"><nav class="nav-side nav-side--left" role="navigation"><menu-tree-help menu-title="Contents"/></nav><article role="main"><h1 id="contentH1"/><div class="caption article__tags content-only-hidden quick-help-hidden"><span class="article__tags--applies-to"> </span><br/><span class="article__tags--role"> </span></div><div id="content-body">&#13; <p class="note"><b>Note</b>: 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 <a href="relate_tables.htm" class="MCXref xref" xrefformat="{paratext}">Relate Your Data</a>. For information about how Tableau handles combining data from different levels of detail (such as many-to-many relationships), see <a href="https://help.tableau.com/current/pro/desktop/en-us/cardinality_and_ri.htm">Cardinality and Referential Integrity</a>.</p>&#13; <h2 is="heading-item" :level="2" id="overcounting-values-or-duplicated-data">Over-counting values or duplicated data</h2>&#13; <p>When you connect to and join multiple tables together, you set up a <i>denormalized</i> 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, <i>all</i> tables are queried. This can cause values to be over-counted. </p>&#13; <p>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.</p>&#13; <p>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 <a href="datasource_relationships_perfoptions.htm" class="MCXref xref" xrefformat="{paratext}">Optimize Relationship Queries Using Performance Options</a>.</p>&#13; <p>To help resolve this issue, you can do one of the following:</p>&#13; <p><b>Relate the data:</b> Instead of creating a join, which might duplicate your data, you can use relationships. For more information, see <a href="relate_tables.htm" class="MCXref xref" xrefformat="{paratext}">Relate Your Data</a>.</p>&#13; <p><b>Change the aggregation:</b> Depending on your analysis, you can use the <span class="uicontrol">MIN</span> or <b>AVG</b> aggregation to remove over-counting.</p>&#13; <p>For example, if you change the aggregation of <b>Salary</b> from SUM to MIN or from SUM to AVG.</p>&#13; <p><b>Create a calculated field that removes the duplicate values:</b> 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.</p>&#13; <p>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: <code>SUM ([Salary])/ COUNT ([Employee ID])</code>.</p>&#13; <p>You could also use a Level of Detail Expression to delete the duplicate data. For more information, see <a href="http://kb.tableau.com/articles/howto/removing-duplicate-data-with-lod-calculations" target="_blank">Removing Duplicate Data with LOD Calculations<span class="sr-only">(Link opens in a new window)</span></a> in the Tableau Knowledge Base.</p>&#13; <p><b>Use custom SQL:</b> 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 <a href="customsql.htm" class="MCXref xref" xrefformat="{paratext}">Connect to a Custom SQL Query</a>. </p>&#13; </p><h2 is="heading-item" :level="2" id="broken-table-joins-and-crossdatabase-joins">Broken table joins and cross-database joins</h2>&#13; <p>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:&#13; <p>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 <span class="uicontrol">Create Join Calculation</span>. For more information, see <a href="#joinoncalcs" class="MCXref xref" xrefformat="{paratext}">Troubleshoot Joins</a>.</p>&#13; <p>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. </p>&#13;