Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!

Nonequi join

Alteryx Partner

I'm trying to do a nonequi join in Alteryx, but the Join tool only supports equi joins.


The attachment is some dummy data that demonstrates the problem.  In the real world I have a FACT table and a Customer Dimension (type 2). 


At the moment I join on the Customer ID only, but I only need the relevant record, so I have to join to create LOTS of records, then an extra filter step for the dates


ie fact.[Report Date] >= Customer.[from date] and fact.[Report Date] <= Customer.[to date]


Obviously on this sort of dataset it's pretty easy, but in the real world I have 20 million records (40GB) in the fact table, so after the join I have over 290 million records (600+ GB) and the next filter drops to 13 million records (28 GB). 


The biggest problem is that I can't do the join "in DB" without some significant refactoring, so I'm hoping there's a solution that I haven't thought of yet.


Is there a more efficient way to do this ?



Inactive User
Not applicable

Try Find and Replace. Find the Customer ID and Replace with the Date(s). The Date fields can be concatenated into one field and your filter can apply parsing logic to determine the dates. Or, preferably In-DB. You can also look to create surrogate keys in the DB to match the appropriate record (highly recommend).

Alteryx Partner

Hi Ryan,

I read your reply when you posted it, but I still don't understand how it would work.


Here's a fully worked example of the problem:


2018-05-18 09_52_44-Alteryx Designer x64 - Type 2 example.yxmd.png


You can see that I only require 3 records at the end and even in this simple example it's only 15% of the records that are joined.


There was also a typo in the file yesterday, so I've attached the new XLS and the YXMD as well.



Inactive User
Not applicable

Sorry Misunderstood parts of your issue, Find & Replace won't work in this scenario. In-DB would be your best option or alternatively you can try using a Calgary Join which should speed up the process greatly.


I was also interested in this.I haven't figured an option to do a non equijoin in Alteryx. Please let me know if you came across anything.