In-Database Conditional Join Anyone?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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..
Solved! Go to Solution.
- Labels:
- Custom Tools
- In Database
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Wouldn't this condition be added in the Filter In-DB tool? Or prior in the Connect In-DB tool?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I agree with @alexnajm that this is a combination of Filter and then Join Tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That sounds great, I'm happy to code the query if needed, which In-DB tool are you referring to?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
