Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
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:
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:
In SAS (assuming the datasets are sorted properly and the join by columns have the same name), you'd do this:
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
