Join by Custom Conditions

[gawa]

I want a feature to enable join by custom conditions. Currently, in Join tool, allowed condition is only equality of specific fields and specific position, however, in SQL, we can join data by much more flexible conditions like;

SELECT TableA.id FROM TableA INNER JOIN TableB ON TableA.id=TableB.id and TableA.value > TableB.value  

Of course, my idea can be easily realized by using combination of Appendix Field + Filter tool, but I meant to say is that Appendix-Fields is quite expensive operation in calculation cost, and it would generate many unnecessary records, which is annoying us in case of handling a huge dataset.

I suppose this kind of flexible conditions can be specified by using expression editor, thereby configuration window of this feature would look like the below image; Adding one more radio button option, and expression editor similar to one used in Filter tool.

Any positive/negative feedback on my idea would be appreciated. Thank you for your attention!

[simonaubert_bd]

Hello @gawa

it is a duplicate of
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Joins-on-Inequalities/idi-p/1646 for in-memory

and https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/In-db-join-with-other-operator-than-...
for in-db

Best regards,

Simon

[gawa]

Hi, @simonaubert_bd 

Thank you for sharing reference of the past similar idea!

My idea is on in-memory process, and aiming to not only inequality but also any conditional statement realized in expression editor, so it should be more generic topic than https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Joins-on-Inequalities/idi-p/1646.

And, my idea is not for in-db process, but in-memory JOIN tool, so I believe my post is still worth of it.

[Chris-Nienart]

Hi @gawa,

This is a good idea, I would appreciate this functionality too.

I took a look at the join code in a text editor. It turns out that the radio buttons for "Join by record position" and "Join by specific fields" control a Boolean called joinByRecordPos. So to add a third option, "Join by Custom Conditions", that configuration option would have to be reworked.

Also the equality in "Join by specific fields" doesn't explicitly happen. Instead, the left and right expressions are kept separately. I created an example where I join two input files on a common field called "Animal". When joining by specific fields, the left field "Animal" and the right field "Animal" are just listed; there is no Left.Animal = Right.Animal statement.

So to build on your idea, I would propose that Alteryx build a new tool called "Custom join" which would have join conditions on any Boolean expressions. That way, the existing join tool doesn't have to change and new tool doesn't have to support the legacy setup. 

[gawa]

hi @Chris-Nienart 

Thank you for your feedback! I strongly agree with you. It is not necessary to customize the existing legacy JOIN tool, but instead, creating a new tool for Custom JOIN would sound better. This approach also prevent the potential risk of malfunction on the existing Workflows created in earlier version and having legacy JOIN tool.

I hope this feature would be implemented in near future.

[cemcintosh2]

Thanks for the input.  Unfortunately the other variable is that sometime as company may have a parent at one point in time and then not have a parent company in the future.  In this case the "Parent Company" is referencing a GPO organization a a customer is a member of.  We have times when the customer may opt out of any GPO groups.  Because the match would be made on the company in both tables, filtering out the dates would then omit their transaction reports entirely in the rare cases that any of our companies moves from having a parent company to not having one.

I'll have to have the join done in another environment until Alteryx can add this functionality.  I'm surprised it does not exists, as this is not an uncommon need.

Thank you/