Suggestion: Specific Info or Warning message when a Join tool creates additional records

Question

I'd like to suggest an enhancement to the join tool. A message, warning or possibly even an option to throw an error when a join tool creates duplicate records.

A significant amount of my trouble shooting time is spent tracking down instances where a join has created duplicate records. This is usually caused by one of the inputs not being unique and the join essentially doing a many to many operation. Sometimes this is desired. Other times it can significantly throw off your results.

To solve, I usually throw "Unique" tools into my workflow to track down the duplication. But this is time consuming and not very efficient. Would be much easier if the designer could tell me if a join has detected a many to many situation and perhaps even give me the option to throw an error the join is about to duplicate data. Would make development go MUCH more quickly.

alexnajm · Answer

This should go into the Ideas section of the Community! That way it can be voted on and Alteryx will evaluate if it’s popular enough to implement.

WeiLi · Answer

it doesn't sound like an error, more like an intended feature. JOIN is a fairly common command in RDBMS, as well as computational mathematics.

that being said, JOIN in Alteryx is not the same as JOIN in SQL, especially LEFT JOIN and RIGHT JOIN operations.

and i usually dont run into the problem that you described either, because i usually use SUMMARIZE to group the listing, eliminating the possibility of duplicates before i pass the data into JOIN. finally, for any serious application of database, you would be storing data via SQL or equivalent and proper etiquette requires that you declare PRIMARY KEY. by definition PRIMARY KEY is always unique and therefore no duplicates. the other reason why PRIMARY KEY gets used a lot is for searching and speed.