Joining Data on the Max Value Less Than My Value

[dmccarville]

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:

1

10

27

Table 2:

2

3

4

11

12

28

29

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?

[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 · Accepted solution]

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

[geraldo]

@dmccarville 

An example

[dmccarville]

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