Reference Unjoined Table in Formula

ethanschuttler

Hi All  - 

I have two tables that I can't join on any one specific field, I just need to reference one for a formula I am building on the other sheet. I could do the formula manually but would like it dynamic in case things change. 

The basics are I have a table of sizes:

And another table with items and dimensions. I need to assign a bin size based on item dimensions and Cubic Volume, but it needs to be in formula form as the Cubic Volumes all vary between each bin size level. For example an item with a .06 CV would need to be a bin size 3 because it is too big for 2, the same goes for any item with a CV above .050926 and below .101852. I will also need to incorporate dimensions as an item that have a CV below .050926 but a dimension that is 6 inches long still wouldn't fit in a size 2.

If you need more information let me know.

Any help is appreciated!

danilang

Hi @ethanschuttler

In Alteryx, there is no way to reference fields that are not in the current dataset.  You have to combine them somehow.  Like @neilgallen said, appending the bin information is the way to go.  In your specific case, the volume of the bin is not a limiting factor since you're boxing solid items with fixed dimensions and not fluids like sand or water that change shape to match the container.  The limiting factors are the 3 dimensions.  The Filter tool removes all bins that have at least one dimension that is too short. After that the summarize tool finds the smallest bin for each item and the join fetches the bin information

Note:  This solution only gives you unique solutions if the bins all have unique volumes because the final join matches on cv.  If you have duplicate bin volumes that pass the filter and minimum CV size, you'll get duplicate bins matching the item

Dan 

Unjoined Table.yxmd