This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi all,
I have a list of products that each can go into 3 particular buckets, say A,B,C (in order of preference). Each product takes up some space and each bucket has a particular amount of space available
I was wondering if I could use a tool on alteryx to allocate each product into each bucket, so for example :
Product 1 --> Potential buckets in order of suitability : B, A, C, Space taken up by product 1 : 50, space available in bucket B = 50 ---> allocate to B
Product 2 --> Potential buckets in order of suitability, B,A,C, Space taken up by product 2 : 50, space available in B = 0, cannot allocate to B --> allocate to next choice (A)
I was looking for a way (maybe a macro) that does this automatically rather than requiring manual processing
Thanks so much!
The general problem is very hard (https://en.wikipedia.org/wiki/Bin_packing_problem)
Given a small scale, I think a macro could be created. Could you provide a full sample set to look at? i.e. all 3 bin sizes, product sizes, product order of preference and quantities
Thanks for the reply - I think (may be wrong) that this problem is easier than the bin packing one as I just require that the two datasets interact with one another e.g.
Dataset 1 : Product 1, Linear space taken up : 20, order of preference : A,B,C
Product 2, Linear space taken up : 30, order of preference, B,A,C
etc etc
Dataset 2 : Available space : A ; 200, B:250, C:300 --> [product 1 fits in A, so allocate to A]
A : 180, B : 250, C: 300
A : 180, B : 220, C:300
etc etc
I just need to go down the list of products (in the order I have) and allocate to A,B,C according to 1) is it the highest order of preference, 2) is there space in that choice, if not allocate to 2nd choice ...
If this requires a sample, let me know and i'll post
Thanks so much
Yes indeed a lot simpler.
Will stick a sample together
Great, I've never used macros on alteryx so may require some clear steps on how you assembled it if possible - really appreciate your time
Thanks a lot for your help!
Trying to implement your tool for my dataset (roughly 2k rows of product) but its taking more than 30 minutes to run and says that its running more than 1TB of data....is this normal ?
Different approach to get speed then...
The attached will use a multi-row formula to compute buckets. The remaining size is held in a concatenated string and broken out using a fairly complex expression.
IIF([Size] < ToNumber(GetWord([Row-1:BucketSizes], [1])), REGEX_Replace([Row-1:BucketSizes], "((\d+ ){" + ToString([1]) + "})(\d+)(.*)", "${1}" + ToString(ToNumber(GetWord([Row-1:BucketSizes], [1])) - [Size]) + "$4"),
Basically it picks the word by priority order. If it is large enough it decreases the remaining size and rebuild a string using a REGEX_Replace.
Its a little fiddly but I think it works correctly and runs in about 0.7s for ~2.8k products.
Thanks - it works for your example but if I change the letters from say ABC to PAB or something, the allocation doesn't work? I think it might be something to do with
IIF([Size]=0, [BucketSizes],
IIF([Size] < ToNumber(GetWord([Row-1:BucketSizes], [1])), REGEX_Replace([Row-1:BucketSizes], "((\d+ ){" + ToString([1]) + "})(\d+)(.*)", "${1}" + ToString(ToNumber(GetWord([Row-1:BucketSizes], [1])) - [Size]) + "$4"),
IIF([Size] < ToNumber(GetWord([Row-1:BucketSizes], [2])), REGEX_Replace([Row-1:BucketSizes], "((\d+ ){" + ToString([2]) + "})(\d+)(.*)", "${1}" + ToString(ToNumber(GetWord([Row-1:BucketSizes], [2])) - [Size]) + "$4"),
IIF([Size] < ToNumber(GetWord([Row-1:BucketSizes], [3])), REGEX_Replace([Row-1:BucketSizes], "((\d+ ){" + ToString([3]) + "})(\d+)(.*)", "${1}" + ToString(ToNumber(GetWord([Row-1:BucketSizes], [3])) - [Size]) + "$4"), [BucketSizes]
Or perhaps another issue - any thoughts?