Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Join Tool generates duplicate values

Alteryx Partner

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/
Alteryx Certified Partner
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?

Alteryx Partner
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.

5 - Atom

Thanks guys... Your post helped

5 - Atom

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.