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 

[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.

[ianjohnston]

@MarqueeCrew 

Hi Mark

Would definitely love to have the functions from the JOIN and UNION tools working as one.

Have often thought about having two tools set up in this kind of format:

• OUTER JOIN - Left Outer Join + Right Outer Join + Outer Join + Full Outer Join
• INNER JOIN - Left Join + Right Join + Inner Join