community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Iterative problem

Alteryx Partner

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

Alteryx Partner

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

Alteryx Partner

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

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

Alteryx Partner

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.

 

 

Alteryx Partner

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?

Highlighted

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