Using Join Tool For Conditional Where Clause
- 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,
How do we use the Join Tool for joining two tables: Table1 and Table2 for conditional statements?
For Ex, if a SQL statement is written in the form of,
select * from FROM Table1 T1
JOIN Table2 T2 ON T1.Field1 <= T2.Field2 AND T1.Field3 > T2.Field2;
How to convert this join statement using Alteryx Join Tool?
Thanks.
Solved! Go to Solution.
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is an interesting question! I would likely use the Connect In-DB and drop your sql into the query builder. If you're not using the In-DB tools, you can perform the same query using a Standard Input.
If your data is already resident on your local machine, this solution depends upon the number of record possibilities in Table2. I can see solving this with the use of append fields if the table 2 rows are small. If there are many rows in it, then perhaps a use of generate rows might be desireable.
The in-db tools join doesn't have an unequal join syntax. Maybe you would like to post that as an idea?
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Being new to Alteryx, i don't seen to have worked out this. In my case all the fields in the Where clause are DateTime. I tried to use the Append Fields tool and then the filter clause but for some reason, i found the data volume on that node reaching in GBs, when i ran the flow.
For the Generate Rows Option, when i ran the flow i kept getting error for below configuration. What am i missing here?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I know working with the Generate Rows tool can be confusing initially. You may have missed it, but in my previous reply I attached a workflow, and you can see the configuration if you open the file.
Here is the configuration of the Generate Rows tool (this exact config will not work for Date data type):
Since you are working with a different data type than your original question, can you provide some sample data that represents your situation, so the configuration can be adjusted to fit your situation? Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It's like this.
The table1 contains two fields as shown below:
And the table2 contains a column as below which is nothing but a an interval of 5 mins:
And we need to perform the join as : entrance_date <= mdh AND exit_date >= mdh
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
How about the attached?
The hardest part is writing a formula to round the entrance_date up to the nearest 5 minute interval. I am sure there are many ways to do this, here is what I cam up with:
IF MOD(ToNumber(DateTimeFormat([entrance_date],"%M")),5)=0 THEN
[entrance_date]
ELSE DateTimeAdd([entrance_date],5-MOD(ToNumber(DateTimeFormat([entrance_date],"%M")),5),"minute") ENDIF
What this does is check if the minutes of the entrance_date is divisible by 5 with no remainder. If there is a remainder, add 5-remainder minutes to the entrance_date.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
This worked perfectly Joe. Thanks for the help.
It would be great to have this handled OOTB by Alteryx.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Joe,
Got another doubt on similar issue so thought of checking with you if you or anyone else is reading this.
Similar to join conditional join over 3 fields, if we have multiple conditions involving multiple fields, how would we achieve it?
For ex, consider the below condition,
Select .................... FROM time_in_zone t1
LEFT JOIN individual_in_shift t2
ON t1.id_individual = t2.id_individual
AND t1.timestamp_start >= t2.swipein
AND t1.timestamp_start <= t2.swipeout
AND t1.timestamp_end >= t2.swipein
AND t1.timestamp_end <= t2.swipeout;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Joe and Marquee,
Thanks for suggesting a couple of workaround. However, as you know this approach may implode in size in many real situations.
To solve this problem, Alteryx should add a node/feature with the ability to add "Where" clause or at least to be able to join two tables with >= or <= condition. Do you know how we can request and escalate this imminent need?
Thank you,
Zane
