Using Join Tool For Conditional Where Clause

[GauravRawal]

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.

[MarqueeCrew]

@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

[Joe_Mako]

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.

[GauravRawal]

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?

[Joe_Mako · Accepted solution]

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!

[GauravRawal]

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.

[Joe_Mako · Accepted solution]

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.

[GauravRawal · Accepted solution]

Hi,

This worked perfectly Joe. Thanks for the help.

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

[GauravRawal]

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]

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