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