Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Using Join Tool For Conditional Where Clause

GauravRawal
8 - Asteroid

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.

13 REPLIES 13
MarqueeCrew
20 - Arcturus
20 - Arcturus

@GauravRawal,

 

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Joe_Mako
12 - Quasar

Attached are two in-Alteryx routes that can be used as well. One can be more efficient that the other depending on the data contents. Option 1 can also be in waves of granularity, for example, incriminating and joining on months, then repeating the process with days.

 

Conditional Where Join.png

GauravRawal
8 - Asteroid

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?

 

Capture.PNGCapture1.PNG

Joe_Mako
12 - Quasar

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):

 

gen rows.png

 

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!

GauravRawal
8 - Asteroid

It's like this.

 

The table1 contains two fields as shown below:

 

table1.PNG

 

And the table2 contains a column as below which is nothing but a an interval of 5 mins:

 

table2.PNG

 

And we need to perform the join as : entrance_date <= mdh AND exit_date >= mdh

 

Thanks.

Joe_Mako
12 - Quasar

How about the attached?

 

gen.png

 

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.

GauravRawal
8 - Asteroid

Hi,

 

This worked perfectly Joe. Thanks for the help.

 

It would be great to have this handled OOTB by Alteryx. 

GauravRawal
8 - Asteroid

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;

ZaneT
6 - Meteoroid

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   

Labels