1 Reply Latest reply on Aug 26, 2016 11:05 AM by Santiago Sanchez

    Linked Data Multiplying Result

    Sean McNally

      Being new to linking data sources I may have made an obvious error.  I have two tables which are linked by a common ID.  When I look at the spend level data it is the spend result multiplied by the number of rows in the linked data source (64 in the attached example).  What am I doing wrong and how can I stop this?

       

      Thanks

        • 1. Re: Linked Data Multiplying Result
          Santiago Sanchez

          Hi Sean, welcome to the world of linking tables (a.k.a. joins)! When you are combining 2 tables via a common field, there are a few situations that may occur:

           

          1. A row in one table matches exactly to a row in the other table (a 1 to 1 relationship).

          2. A row in one table matches 2 or more rows in the other table (a 1 to many relationship).

          3. A row in either table matches 2 or more rows in the other table (a many to many relationship).

           

          In your example, sounds like we are in the second situation. If we use CASE ID as the common column, there are several rows on the [KPI Data] table that match a row in the [Data] table. That seems to be the case because each table has multiple CASE IDs by Date.

           

          The fix depends on your data and what you are trying to do. 2 alternatives that come to mind are:

           

          a. You may join by CASE ID and DATE, assuming the dates between the 2 tables should match.

          b. You could create a calculated field and filter out duplicates. See green tabs on attached workbook for an example. [Month Matches? (Remove Duplicates)] assumes the month (not the full date) should match on both tables.

           

          This short video may also help with some of the concepts behind joins: Join Types with Union | Tableau Software