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!

[neilgallen]

@ethanschuttler depending on the size of your data, an "append fields" tool would get you there. In this scenario it would append every bin size to each row of your dataset, in effect creating duplicate rows. You could then filter for the row where the cubic volume is within the cubic volume range. In this scenario you'd need to set a min and max CV range in individual fields, however. This is easily done with a multi-row formula tool.

Sorry I can't work out an example, but happy to help later if no one else gets there first.

[danilang · Accepted solution]

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 

[ethanschuttler]

@neilgallen and @danilang thank you both!!