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.

[Joe_Mako]

@ZaneT What situation do you have in mind where this approach would implode in size? An example before and after would be ideal.

One option may be to perform the Generate Rows and Join combination multiple times with increasing granularity.

If we can identify the common situation, we might be able to make macro.

[ZaneT]

Hi @Joe_Mako

I used your workaround for my dataset because my data was daily and its size was small (thank you for the hint!).

But imagine that we have traffic or online shopping data and each event is recorded in seconds (or mili-seconds) in table T1. If we want to join this table with a look up table (T2) with start and end times, one would need to Generate 3600 rows (or 3600x1000) for each one hour time interval in the lookup table.

Again, a professional database management/data analytics system like Alteryx should have this GT or LT feature as the core basis of its query writing, or should provide Custom Query writing like many other application (like Hyperion which is based on the 90's technology).

Just my 2 cents.

Thanks again,

Zane

[Joe_Mako]

@ZaneT can you make some sample data date for this T1 and T2? Here would be my initial approach:

1. Use Filters tools in sequence to split T2 into groups with different length thresholds, eg less than second, less than a minute, less than a hour, less than a day, etc

2. For each length stream, Generate Records for the next smaller unit, eg seconds generate milliseconds, minutes generate seconds, hours generates minutes, days generates hours, etc

3. Use a Formula tool on T1 to add fields that truncates the date time to each unit

4. Use Join tools for each unit level

5. Union the streams

6. Filter to get matching ranges.

If you can provide the sample data that represents the situation, I can make a macro for it.

This could also work for number ranges as well, just use ranges units like 1,100,10,000, etc

[ZaneT]

@Joe_Mako

Hey Joe,

I don't have the data now, but that was the kind of data that I used to deal with in my previous jobs using SQL.

Thanks for the hints, but I hope you agree with me that a join like that should be straightforward and we should not spend time finding tricks.

I hope Alteryx developers/engineers give a very high priority to this functionality. As I am using Alteryx more, I am liking it more and more. Hopefully, enhancements like this will make its usage more widespread.

Thanks again,

Zane