community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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 ?

 

 

Alteryx Certified Partner

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.

 

 

Alteryx Certified Partner

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.

Atom

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.

Labels