Various enhancements to Join Tool

[TomWelgemoed]

Hi,

This idea is to suggest we add additional comparison capability to the Designer Join tool to more closely mirror what is possible in SQL. Usually, teams work around this limitation by adding tools after the Join tool (or using SQL queries at the start of a workflow), but it would be great to do this in the Join Tool itself.

The current Join Tool in Alteryx only allows for exact field comparisons, but not for more flexible joins, such as the example below

LOAD * FROM Table A

LEFT Join Table B

ON A.Firstname=B.FirstName

And A.Country <> B.Country

AND A.Lastname LIKE (B.Lastname)

OR A.Nationality = B.Nationality

I've drawn a mock-up of what I think could be enhancements to the existing tool. In simple terms, I think there are these options for improvement:

1. When joining on specific fields, allow the user to specify operators for how the fields should join, e.g. Field A=Field B and Field C  !Contains  Field D (almost like the Filter Tool does this). This could be enabled by permitting operators in the menu between the 2 fields, as illustrated below

2. Have the option to say that if either field matches, you'd like to join (i.e. option to choose if this is an AND or OR join condition). A new field would need to be made to the left of the field selection, as illustrated below

3. Perhaps there could be the option to write the expression as you would a formula for the Error message tool (but in a positive context), e.g. Field A != Field B

4. How about enabling the Left Join/Right Join/Full Outer Join options from within the Join tool? E.g. if you select the Venn Diagram buttons, you will return your selection? If you don't want to compromise existing capability, you can automatically add a pre-configured Union tool (with the correct left/right/join inputs) after the Join tool based on the user's selection?

Regards,

Tom

[Joe_Lipski]

Love the idea of making the icons clickable to make outer joins possible. This fits nicely into the common design patterns that has been discussed/blogged about a fair amount recently!

[TomWelgemoed]

Thanks @Joe_Lipski for the support! I have to catch up on the common design patterns, although I did see a good one about making containers more useful (to allow workflow sequencing) pinned to the ideas board. Take care.

[Joe_Lipski]

https://community.alteryx.com/t5/Engine-Works/5-MORE-Useful-Design-Patterns-in-Alteryx-You-Need-to-M...

[TomWelgemoed]

Thanks Joe - helpful - see what you mean. Useful food for thought as well - maybe some of these scenarios should be new tools/macros etc.

[KylieF]

Thank you for the idea!

Its super helpful for an idea to have such thorough details and graphics so our product team can fully grasp the request and pain points being brought up. Be sure to share this idea with other who you think might benefit as likes helps the product team also gauge how valuable this would be to our user base.

[haraldharders]

Hi,

I'm not sure whether it's the right way to add my thoughts at this point in time. Please let me know if I shall open a new feature request.

In addition to the features proposed by @TomWelgemoed, I would like to propose two additional extensions:

1. For the "Unknown" columns in the list of columns, distinguish between "Unknown left" and "Unknown right" columns. This would be helpful in quite a lot of cases when incoming columns vary: E.g. add one specific column of table B to the full content of table A. In that case, we would switch on "Unknown left" but switch off "Unknown right". This would avoid putting a select tool in front of the join.
2. All reporting of data stream towards Left, Join, or Right output. This would help making debugging easy.

See my mock-up picture below (new features in red). 

Best regards

Harald