Why is join causing raw file to duplicate fields five-fold?!?

[Nameless_One]

Hi everyone,

I'm trying to do a join on a raw fixed asset file with a separate file that has a key for the "Asset Category Type" for the assets in the raw source file. When I do a join between the fixed asset (i.e., main) file with the asset category (i.e., key) file, I get a result which duplicates my raw file 5-fold! I am so lost. Would really appreciate any help/guidance the community can offer.

Thanks so much!!

[gabrielvilella · Accepted solution]

The join will duplicate records if the key field you used has duplicated values on the input tables. Assuming that the duplicates are on the table on the L side, you can remove duplicates with a Unique tool before the join.

[Adrian_T · Accepted solution]

Hey @Nameless_One,

This is an expected behaviour and is due to the cartesian product nature of the Join Tool. This means that every row from the left input data is matched with every row from the right input data, as explained in this article.

As @gabrielvilella has rightly pointed out, the way to avoid this will be to ensure that there are no duplicates in your datasets before joining them, using the unique tool. 

Hope this helps!

[Nameless_One]

Thank you! I realized my key had several duplicate values in the primary key identifier, which is the main focal point of the join. I fixed the workflow to account for the issue.