Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Conditional Join

Highlighted
7 - Meteor

Hi all,

 

I’d like to do an inner conditional join:

Table 1                            Table 2

Var Date                         Var Date

A     01/10/15                 A     28/09/15

B     02/11/15                 A     14/10/15

C     12/12/15                 A     17/10/15

 

I’d like to merge on:

Key1 = Var

Key2 = Date (only if Date in Table2 is greater than Date in Table1)

 

In our case, the result of the inner join is:

 

JoinTable

Var Date

A     14/10/15

A     17/10/15

 

Can we do that with Alteryx?

 

Thanks,

 

Franck

Highlighted
Alteryx Partner

Hi,

 

First join by VAR key fields, then Filter Tool where [Right_ date] is greater than [Date]

Highlighted
Alteryx Alumni (Retired)

"Conditional joins" are a common question for Alteryx users. With the Join tool by itself, there isn't a way to do this, But you can with a combination of tools.

 

Do a search on the Community for "conditional join". There are a few different articles in there that might give some different ways to get what you want. In particular, this one references the Advanced Join tool that you can download from the Gallery. You might want to check it out.

 

In the meantime, the attached workflow gets you the results.

Highlighted
7 - Meteor

Very disappointing that Alteryx cannot handle a such a simple join...

Highlighted
8 - Asteroid

I think the following will help:

 

Join both of the tables thru' Append Fields and insert a Filter where you use custom filters and pick the variables and relate them with operators and &&,I etc depending upon the conditions (like this: [Var]=[Variable]&&[Date]<[D]).

Highlighted
6 - Meteoroid

Although there probably is a work-around, doing a join and then filtering on the condition is not the same as doing a multi-conditional join.

 

For example let's say I have 2 tables: 

VARDate1Date2ID
A1/1/20202/1/20201
B3/1/20204/1/20201
C5/1/20206/1/20201

 

and

 

VARDATE
A1/2/2020
B5/1/2020

 

 

And let say I wanted a left join only on VAR and if DATE is in between Date1 and Date2. If I did it in SQL

 

SELECT * FROM TBL2 LEFT JOIN TLB1 ON TBL1.VAR = TBL2.VAR AND TBL2.DATE BETWEEN TBL1.Date1 AND TBL1.Date2

 

I would expect:

 

VARDATEID
A1/2/20201
B5/1/2020(NULL)

 

But when you do it in Alteryx with a join and then a filter, it joins on Var = B, but then the filter to see if DATE is in between Date1 and Date2 will eliminate that row so that your result is:

 

VARDATE
A1/2/2020

 

The biggest issue with this Alteryx way of doing things is that it will eliminate all the rows where one of the conditions meet, but not the other[s]. Maybe there is a work around here, but it'll be messy, and I feel like multiple condition joins is something Alteryx definitely should add to the next build.

Labels