I have two data streams, Table1 and Table 2. Each table has a single column, ID. I'd like to join them, but the join condition is unusual. I'd like to join an item from Table1 to Table2 where the Table1.ID is the maximum value less than Table2.ID. Example below. Table 1: 11027 Table 2: 23411122829 Desired output: Table1.IDTable2.ID1213141011101227282729 Conceptually this is just a join with a subquery, but I haven't figured out how to approach this in Alteryx. Has anyone worked with this kind of challenge?

Joining Data on the Max Value Less Than My Value

I have two data streams, Table1 and Table 2. Each table has a single column, ID. I'd like to join them, but the join condition is unusual. I'd like to join an item from Table1 to Table2 where the Table1.ID is the maximum value less than Table2.ID. Example below.

Table 1:

Table 2:

Desired output:

Table1.ID	Table2.ID
1	2
1	3
1	4
10	11
10	12
27	28
27	29

Conceptually this is just a join with a subquery, but I haven't figured out how to approach this in Alteryx. Has anyone worked with this kind of challenge?

Help

Join

Accepted answers

SPetrie

One way would be to just append them to each other, sort and then grab the ones that meet the criteria.

You can also get more involved with an iterative macro to take the values one at a time and check if they meet the criteria.

I probably overengineered my macro, but it was a fun exercise

examples.yxzp

All comments

apathetichell

How many rows are we talking about? thousands/hundreds of thousands or more? What would happen if an item in table1 and table 2 matched? would it match go with the prior entry in table 1 or the current entry?

I'd probably use multi-row formula to get the next value in table1.ID into a column - use generate rows with table 1 to create a matrix of possible values up to that new column. I'd join the [RowCount] from the generate rows to Table2.ID. I then drop [RowCount] and have your output.

SPetrie

One way would be to just append them to each other, sort and then grab the ones that meet the criteria.

You can also get more involved with an iterative macro to take the values one at a time and check if they meet the criteria.

I probably overengineered my macro, but it was a fun exercise

An example

@SPetrie I learned about 10 different things from your post. Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703