Join two datasets based on one column in data set 1 and 2 columns in second data set

[AmitBelagali]

Hi All,

I have a unique requirement. I have two datasets. I will need to combine both datasets. The join should happen based on a value in dataset one and see if the value is greater than one column and smaller than in next column in dataset two. Example, I need to join say "number" column that has value as 2 from data set one and join with dataset two wherein column "from_number" is 0 and "to_number" is 5. How can I acieve this?

Thanks,

Amit Belagali

[OllieClarke · Accepted solution]

Hi @AmitBelagali 

You can use an Append Fields tool (https://help.alteryx.com/20223/designer/append-fields-tool) to cross-join both data sets (ensure you have set to allow all appends). Then a filter tool will let you set up this 'join'

[Value]>=[From_number]
AND
[Value]<=[To_number]

Hope that helps,

Ollie

[Christina_H]

Append and filter would work, but depending on the size of the datasets it might be better to use generate rows to create all values in dataset 2 and then use a regular join.

[alexnajm]

Would this macro work? Join on ranges - Alteryx Community