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.
Wouldn't this condition be added in the Filter In-DB tool? Or prior in the Connect In-DB tool?
I agree with @alexnajm that this is a combination of Filter and then Join Tool.
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..
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.
That sounds great, I'm happy to code the query if needed, which In-DB tool are you referring to?
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...
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.
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
User | Count |
---|---|
106 | |
82 | |
70 | |
54 | |
40 |