Doing a dynamic BETWEEN with 2 local data sources?

[Hamder83]

Hey guys 

I'm new to alteryx, and im trying to achieve something similar to the BETWEEN in sql.

I have 2 data sources.
One with Product and Weight - 

And i wanna get the price for that product from a 2. file, that contains a min. weight and a max weight.

Its something like this:

A.Shipment_type	Weight
Parcel		1
Parcel		2
Parcel		7
Select [Price] from b.price_table where A.shipment_type.Weight BETWEEN B.Min_Weight AND B.Max_Weight
B.Price_table
Type	Min Weight	Max Weight	Price
Parcel	1	5	50
Parcel	6	10	100

Any ideas how to achieve this?

[LordNeilLord]

Hey @Hamder83 

On you b. table you can make use of generate rows to split the range into individual rows....here's a good blog on how to use the tool: https://www.thedataschool.co.uk/megan-hunt/alteryx-basics-generate-rows/

Alternatively you can join the tables using "type" as the join field and then use a filter after to mimic the "between" function

[Jean-Balteryx · Accepted solution]

Hi @Hamder83 ,

I attached a workflow performing what you want if I correctly understood your need.

Basically, it generates the weights in each range, then joins A and B using the weight and keeps the ranges from the B table that matched with A table data.

Don't hesitate to ask for more details.

Have a great day !

Kind regards,

Jean-Baptiste

[Hamder83]

Hi 

This was excactly what i was looking for. I was thinking to manually enter the weight interval as your Generate row does automatically! 

I highly appriciate the solution 🙂