Joining & Unioning in 1 tool

[MarqueeCrew]

The JOIN tool could use some love.  Let's consider merging the JOIN and UNION functions into a single tool.  Instead of strictly L, J, and R outputs, we could have an option to allow for all standard SQL joins:

• Cross Join (Warning!!!)
• Inner Join (boring)
• Left Outer Join (saves time configuring Union)
• Right Outer Join (saves time ...)
• Full Outer Join (saves time ...)

Being able to JOIN on case-insensitive values is a big bonus (resisted urge to BOLD and change font size).

Being able to JOIN on date-range is often requested.

Being able to JOIN on numeric-range is often requested.

If we are combining tools, getting UNIQUE on L or R (or both) inputs would also save time.  Most JOIN errors are because the incoming (R) data contains duplicates by KEY.

cheers,

Mark

Hi @NicoleJ 

[TonyaS]

Thanks @MarqueeCrew !

We were literally just talking about the case-insensitive Join idea/request on a call today. It's on our list to meet with Nicole about it soon. 

[MarqueeCrew]

Maybe also options and warnings about null join fields?

[Kenda]

Lots of suggestions/ideas packed into one post @MarqueeCrew! I particularly would love the ability to do a case-insensitive join!! 

[Kenda]

While we're on the topic, go check out some other improvements to the join tool here: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Add-Ability-to-Reorder-Fields-Being-Joined-i...  :) 

[fmvizcaino]

Yes, please!!! 🙌🙌🙌🙌

[marcusblackhill]

It would not only save me time cleaning data, but it would also save me time investigating why the join didn't work as expected. I particularly would love it if the match was case insensitive.😁

[terry10]

It would also be nice if the Join & Unioned output applied renames and reordering from the embedded select. Currently the R and L output do not apply these renames, so I haven't been using the embedded select.

[simonaubert_bd]

Hello,

Well, I'm not a fan of the tool that does everything for readibility reason. It's like the tmap in Talend, the tool is so complex that you need a training for just this tool.

Best regards,

Simon

[DavidP]

Awesome ideas @MarqueeCrew!!!

I think explicit warnings on the generation of duplicates would solve a lot of problems in workflows.

Also, configurable workflow messaging un un-joined records.

[MarqueeCrew]

Yes @DavidP 

if the metadata knows that there are duplicate keys, Warren while configuring. (Example: Right Input data has duplicate data if you join on xxxx field)