Cardinality
may affect the results returned when joining tables because joining tables that
have one-to-many
or
many-to-many
relationships
can have the effect of duplicating values or multiplying rows by the number of
times a primary key’s match appears in the secondary table.
In the diagram below you can see the left table
has a one-to-many relationship with the right table because there are multiple
prescriptions for the same patient ID. When these tables are joined, the result
table shows that for every time Patient ID 395959 is matched with a
prescription in the right table, the values from the left table duplicated the
same number of times. Although the payment due for Patient 395959 should be
$2000, aggregating the values in the result table produces the incorrect amount
of $6000 ($2000 x 3 or the number of times the patient ID appears in the right
table).
Solution:-
Tableau
offers several solutions for addressing duplication of records when joining
tables that have a one-to-many or many-to-many relationship.
1)Changing the
aggregation from SUM to AVG, MIN, or COUNTD may sometimes resolve the issue.
2)Other times, creating a calculated field, using a table calculation or choosing
to blend the data will provide a solution. 3)Custom SQL is another way to remove
duplicates.
The technique you use is dependent upon the data as well as what
you are trying to accomplish.
Thanks Guru's,
Prazval.ks
