Documentation

Avoid Row Duplication (M2M)

When joining multiple tables, you might have noticed that the resulting data set does sometimes not match the results you’ve expected. In this case the data probably gets unintendedly duplicated, as for each unique value in one of the tables, the join takes all of the corresponding duplicate records from the other table.

 

A reason for this is the relationship type between your tables. We differentiate between the following valid relationship types that you can use in datapine to join data from multiple tables:

 

1) One-to-One relationship: In this case, both fields that are used to join the tables have unique values for each row.

 

2) One-to-Many relationship: In this case, one table contains unique values, but the other table contains duplicate values for at least one of the corresponding values in the first table.

 

How to prevent wrong results due to row duplication

 

In datapine, you have the option to prevent row duplication when querying fields with one-to-many relationships in the Analyze section using datapines Duplication Wizard. Our Duplication Wizard runs in the background and detects possible situations where a duplication of the result set might occur and notifies you accordingly. You will need to enable the Duplication Wizard in your Account Settings before you can use this function.

 

How to enable the Duplication Wizard

 

 

  1. Click on Settings in the upper right corner of the navigation bar.
  2. Select the Account tab on the right.
  3. Click on Edit, so you can make changes to the account information below.
  4. Check the checkbox next to Duplication Wizard at the bottom of the account information.
  5. Save your changes.

  enable duplication wizard in datapine

 

How to apply the Duplication Wizard

 

In the Analyzer, our Duplication Wizard now runs in the background and detects possible situations in which a row duplication might occur. In such a case, you will get notified by three little squares showing up next to the field you measure on the Y-Axis.

 

how to apply duplication wizard in datapine

 

Click on the squares next to your measure to open the Table Relationship Engineer. In the Table Relationship Engineer Popup you will see the list of all applied table joins of your chart. A green square next to the table’s field tells you that this field contains only unique values, while three little blue squares will signal that this field contains multiple entries for the corresponding value from the other table. For these joins you can simply prevent row multiplication by selecting either the checkbox next to prevent duplication (1) or by clicking the duplication icon next to the individual table join (2). Please make sure you apply your changes to the chart (3).

 

overview of datapine's Table Relationship Engineer

 

Note: All changes made to the table joins in the Analyzer apply only to the respective chart and do not affect any other chart on your dashboards.