In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

In-Database Conditional Join Anyone?

BobbyD
6 - Meteoroid

Hi Experts. I'm new to Alteryx but surely others have asked this question in the past?

I need to perform an in-database left outer join conditional on a date field being within a range e.g.

 

Where A.Date >=B.DateFrom and <= B.DateTo

 

I've been using the "Join In-Db" tool which gets me part way there, but cant find a sensible way to do this.  Cartesian joins and filters are not the answer for me.

 

Any suggestions?  Coming from a SAS and SQL background, I cant understand how this functionality is not obvious..

8 REPLIES 8
alexnajm
18 - Pollux
18 - Pollux

Wouldn't this condition be added in the Filter In-DB tool? Or prior in the Connect In-DB tool?

Raj
16 - Nebula

I agree with @alexnajm that this is a combination of Filter and then Join Tool.

BobbyD
6 - Meteoroid

Thanks for the quick reply Alex. I had to move the data in-db in order to use the only tool I could find to do a previous left outer join and I'm now joining to data that is sourced in-db.  I'll take a look at the Filter In-Db tool to see if that helps..

 

cjaneczko
13 - Pulsar

You can do this all with the In-DB tool without a filter or join tool. You can structure the SQL statement in the IN-DB tool to JOIN the two tables as well as use the WHERE clause you proposed in your question. 

BobbyD
6 - Meteoroid

That sounds great, I'm happy to code the query if needed, which In-DB tool are you referring to?

BobbyD
6 - Meteoroid

Ah, I see what you mean, I can code up the query using the Connect DB tool.  That feels like an omission by Alteryx but I'll use it...

cjaneczko
13 - Pulsar

Correct, its the Connect IN-DB. I have a few workflows that are joined via the one tool using a similar WHERE statement related to dates. All of the SQL to join and "filter" are done right in the Connect IN-DB. If you weren't savvy in SQL you can also use the Visual Query Builder to setup the joins and filters. 

BobbyD
6 - Meteoroid

Hi all. back again I'm afraid..

The Connect In-DB tool works great for multiple left outer joins but as soon as I include a <= conditional join, the tool fails with an "Error parsing XML" error.

I'm using an ODBC DSN Connection - is this appropriate?

 

The full query including <= conditional join works perfectly directly in SQL Server

Labels
Top Solution Authors