Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
alteryx Community

Alteryx Designer Discussions

Iterative problem

6 - Meteoroid

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!

9 REPLIES 9
ACE Emeritus

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

6 - Meteoroid

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

ACE Emeritus

Yes indeed a lot simpler.

Will stick a sample together

6 - Meteoroid

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

ACE Emeritus

Sample attached.

The iterative macro assigns one product at a time to a bin. If no Bin has enough space it assigns it to `?`.

A little bit fiddly but think should work. Happy to talk over any questions you have with it

6 - Meteoroid

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 ?

ACE Emeritus

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.

6 - Meteoroid

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?

ACE Emeritus

I have done an updated version which will cope with different Bucket names.

It will also allow more than 3 buckets but the priority string can only be 3 long at present.

Labels