Find matching range in another dataset

Solved!
yankees9920
Level 1
Find matching range in another dataset

I have a dataset of properties and their prices

Address Price
123 Main St450,000
1 Broadway750,000

and another dataset that contains scores based on where the price falls within the range

LowRangeHighRangeScore
0499,99910
500,0001,000,0005

I'm looking to append a row to the first table that does a lookup to see what the score should be and would expect the following output.

AddressPriceScore
123 Main St450,00010
1 Broadway750,0005

I can't join these tables since they have no IDs in common and I haven't figured out a way to reference another dataset in a formula during the Prepare step. Any help would be appreciated!

0 Kudos
1 Solution
AlexT
Dataiker

Hi @yankees9920 ,

You should still be able to use a Join recipe in this case but with multiple join conditions with <= and >=
Screenshot 2023-03-13 at 12.08.03.png

 

Let me know if that works for you!

In my example LowRange is min and HighRange is max. Output was :
Screenshot 2023-03-13 at 12.05.58.png

View solution in original post

2 Replies
AlexT
Dataiker

Hi @yankees9920 ,

You should still be able to use a Join recipe in this case but with multiple join conditions with <= and >=
Screenshot 2023-03-13 at 12.08.03.png

 

Let me know if that works for you!

In my example LowRange is min and HighRange is max. Output was :
Screenshot 2023-03-13 at 12.05.58.png

yankees9920
Level 1
Author

Thank you very much, not sure how I missed that.

I have multiple scoring ranges/criteria that I use and I just discovered that I could use pre-joined computed column to further filter each join by the appropriate type

0 Kudos