Joins are a vital part to any analysis. Relating data together is the backbone of bringing data together. Currently the Join Tool allows the relating of one or many data fields that are assigned to be equal to each other (aka Equi-Join).
As creativity evolves and users aspire to construct more complex relationships, Non-Equi relationships become more prevalent. What are Non-Equi relationships? Simply put, they are Non-Equal. Examples:
- Field_A < Field_B
- Field_C > Field_D
- Field_E <= Field_F
- Field_G >= Field_H
- Field_I != Field_J
Non-Equi relationships are especially useful when working with dates that fall within a range of dates contained within two other fields.
- Example: Target_Date BETWEEN Low_Date AND High_Date
Currently, to accomplish this, there are a couple options:
1. Generate Rows:
- You can use the Generate Rows Tool and fill in the range of values
- Then use the traditional Equi-Join matching the generated date to the Target_Date
- And persist only the INNER join stream
Lots of tools to accomplish a "simple" task
<or>
2. Cartesian Join:
- You can use the Append Tool to replicate every Target_Date onto every row containing the Low_Date and High_Date fields
- Filter where the appended Target_Date is between the Low_Date and High_Date
Memory intensive, creating many unnecessary data rows, and may ultimately not work with large datasets
A simple solution, or alternative, would be to enhance the existing Join Tool to allow for choice in the "Join by Specific Fields" configuration section. For example:
Adding in a drop-down menu per field pairing, the additional Non-Equi options could be added. Equal would be the default, but users could otherwise pick the relationship type to accomplish the same "between" condition.
Here's a zoomed image of the look and feel:
The benefit is a much simpler configuration within the workflow, avoiding extra tools and creating a bunch of extra data rows that aren't relevant to the result.
If you're reading this and would like to see this enhancement to the JOIN Tool, consider a quick click on the like button. It helps ideas like this get more exposure and lets Alteryx know this is important to you!
Cheers and thanks for taking the time to consider this idea! -Jay (jrlindem)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Acknowledgements:
It's important for me to point out that this isn't the first time this idea has been posted. There are others that share the sentiment for both Non-Equi Joins as well as other enhancements to the join tool. Here are two other, older, ideas that share some of the same needs:
