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

Question

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!!

Retirements Check - Deferred True-Up Assets.yxmd

Capture.PNG

gabrielvilella · Accepted Answer

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 Answer

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!