Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
NeilR
Alteryx Alumni (Retired)

Have you ever noticed that you can't do a left join or a right join in the traditional sense in a single step with Alteryx? Normally to accomplish this you'd add a union tool after the join like so:

 Left join

I thought it would be nice to reduce this to one step, as pointed out here. While I was at it, I thought I'd add some functionality to filter the data in the same step, reducing the need to add a filter tool after the join, as is often the case. Sandeep expressed an interest here in being able to do this with date ranges. 

 

Attached are some example uses of the macro I came up with to accomplish this. The first example shows a left join. The second example, "Inner join with condition (date range)", performs an inner join on a certain column, then filters the joined dataset by checking if a date from one incoming dataset falls between two dates from the other. In SQL you would accomplish the same thing like so:

 

SQL join

 

In SAS (assuming the datasets are sorted properly and the join by columns have the same name), you'd do this:

 

SAS join

 

Another thing you can do with this macro is a cross join (Cartesian product of rows from incoming data) with an optional filter condition. But be careful, before applying the filter, the resulting join will contain as many rows as the product of the number of rows of the incoming datasets. Meaning if you're joining 2 datasets that each contain a million records, the resulting dataset will contain one trillion records! A potential use for the cross join is illustrated in the third example.

 

Feel free to download the macro and tailor it to suit your needs.

 

-Neil

Neil Ryan
Sr Program Manager, Community Content

Neil Ryan (he/him) is the Sr Manager, Community Content, responsible for the content in the Alteryx Community. He held previous roles at Alteryx including Advanced Analytics Product Manager and Content Engineer, and had prior gigs doing fraud detection analytics consulting and creating actuarial pricing models. Neil's industry experience and technical skills are wide ranging and well suited to drive compelling content tailored for Community members to rank up in their careers.

Neil Ryan (he/him) is the Sr Manager, Community Content, responsible for the content in the Alteryx Community. He held previous roles at Alteryx including Advanced Analytics Product Manager and Content Engineer, and had prior gigs doing fraud detection analytics consulting and creating actuarial pricing models. Neil's industry experience and technical skills are wide ranging and well suited to drive compelling content tailored for Community members to rank up in their careers.

Comments