Conditional Join

[FranckG]

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

[Federica_FF]

Hi,

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

[RodL]

"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.

[Jake_Jake_Jake]

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

[urezwan]

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]).

[AlleghenyAnalyst12]

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: 

VAR	Date1	Date2	ID
A	1/1/2020	2/1/2020	1
B	3/1/2020	4/1/2020	1
C	5/1/2020	6/1/2020	1

and

VAR	DATE
A	1/2/2020
B	5/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:

VAR	DATE	ID
A	1/2/2020	1
B	5/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:

VAR	DATE
A	1/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.

[Lisa_M]

I've been using Alteryx for 4 years now, three updates a year, and not once has this been approached.

I have had some success with the conditional join macro you can download.  Still not ideal tbh.

[AlleghenyAnalyst12]

Lisa_M, can you send me a link to this conditional join macro, I'd like to take a look to see if it's a fool-proof option.

[Lisa_M]

The link is shared above.  It's not foolproof though.  In fact, it's largely more trouble than it's worth.  I do any conditional joins in the input tool where I'm connecting to my data source instead - at least you can do proper joins there.