Join Tool generates duplicate values

[romano_felipe]

Hi,

I am using the Join tool, based on a field that is a 8-digit number. I receive a warning that joining using double or float fields is not recommended.

When I analyze the results, the L and J outputs are generating duplicate values, i.e. the same row is present in both outputs.

I have tried converting the lookupt fields to strings, using the Formula tool, but then I receive an error (cannot have a string that is actually a number as output of the Formula tool).

How can I eliminate the duplicate output problem in the Join tool?

Thank you/

[kane_glendenning · Accepted solution]

Hi Felipe,

The situation you are describing should not happen. As a representation of this, if you look at the Venn diagrams in the Join tool, none of the outputs overlap. It is possible for a record to appear multiple times on the J output as it may match multiple records from the right. The error that you talk about with the formula field should be able to be resolved by using ToString(), however if they are whole 8-digit numbers, then I recommend a Int32 type, you can change this using a select tool. 

The recommended troubleshooting ideas that I have for you are:
1. Sum the record counts before and after the Join tool to work out how many duplicate records you are getting.
2. As a separate datastream before the join, run a summarise with (Group BY: ID Field, Count: ID Field) anmd then sort descending by Count to make sure that there are no duplicate ID Fields.

If that doesn't lead you to a solution to your issue, are you able to post data?

Kane.

[romano_felipe]

Hi Kane,

You were right: the root cause of the problem was the duplicate ID Fields from the right data stream. Once I removed the duplicate entries via the Unique tool, the Join tool started working just as expected.

I greatly appreciate your help.

Felipe

[VanBChironi]

Thanks guys... Your post helped

[UltiMatt]

Thanks for posting your solution. I had a few fields displaying double the correct quantity but only after the join tool was used. Turns out I had a few duplicate IDs and never would have thought to check for uniques until you said something.

[PriyankaaJ]

Thank you for providing options for troubleshooting, Kane. I just spent an hour wracking my brain to understand why my workflow was giving an error saying it was appending duplicate values into an Access database, even while I had a Unique tool before a Join tool. All I had to do was move the Unique tool after the join and all is well with the world! Phew!

[ChrisK2]

---

@kane_glendenning wrote:
[...]
The situation you are describing should not happen [...]
It is possible for a record to appear multiple times on the J output as it may match multiple records from the right. [...]

[...]
Kane.

---

My dude I am so glad I found this. Thank you!