Optimization tool configuration help

[Malsadeh]

I've attached the dataset, which includes the warehouse name along with vendor pricing in columns I, J, K, and L.

My objective is to determine the lowest total cost while meeting the constraints (in pounds) provided.

The Alteryx workflow is also attached for reference.

I'm only allowed to use one vendor per warehouse.

[alexnajm]

I'm admittedly not as comfortable with this tool so it will take me more time compared to someone else perhaps, but I might take a look here to help start setting it up: Tool Mastery | Optimization - Alteryx Community

[Qiu]

@Malsadeh 
I studied the link given by @alexnajm and from there I found this one is more helpful.
https://knowledge.alteryx.com/index/s/article/Optimization-Tool-Entering-a-Model-Manually-1583460627...
I can now only work with Manually input and somehow, my flow will work for upto 4 wareshouses and will fail for 5 and above.
I am wondering if there is any limitation on the number of variables?
@gawa @AkimasaKajitani 
It is time for you to save us here. 😁

[caltang]

I'm not good with the tool either so this is helpful! Thanks @Qiu @alexnajm 

[gawa]

@Qiu 

As I'm not good at the Optimization tool, when I face this kind of puzzles, I always start with brute-force approach. In this case, we have 8 warehouses x 4 vendors;4^8=65,536 patterns shall be examined and it's still easily doable scale with Alteryx to calculate.

- Create all patterns of selection of vendors for each warehouse.

- Check all patterns if constraints are met

- Find the minimum cost among the above met patterns

@Malsadeh 

Just a side note. In this kind of problem, there can be another constraint that 'each vendor shall be selected at least one time'. Without considering this constraint, an optimized pattern is consisted of only vendor 'I' and 'J'.

I'm not sure this is the case for your problem, but I configured this logic into the attached workflow.   

[Qiu]

@gawa 
Thanks for your hands out and the flow is super impressive. I definetely need some time to study it.

[AkimasaKajitani · Accepted solution]

Hi @Malsadeh 

The important point to use the Optimization tool is to create the constraints formula. You have to add all the constraints to the constraints formula.

The most difficult constraint of your constraints is that I'm only allowed to use one vendor per warehouse.

Normally the type is "C" because you want to decide the pounds. But I could't add the constraint "one vendor per warehouse". So, instead of using type "C", I decided to use type "B". And then I choose cost x primary lbs as coefficient. By doing so, I can add the constraint "one vendor per warehouse". That constraint formula is AI+AJ+AK+AL=1.

For A input:

For B input:

For O input:

Answer :

If you can allow multi vendor for the warehouse, the result is as follows.

@Qiu fun problem

[Qiu · Accepted solution]

@AkimasaKajitani thank you very much. Let's invite another two Japan ACE to the conversation, 😄

@DaisukeTsuchiya  @Yoshiro_Fujimori 

[Malsadeh]

Hi Nebula,
Thank you for taking the time to share your workflow! It's the first one I've reviewed, and I’m genuinely impressed with how you approached and solved the problem.

What stands out the most is the dynamic design—you've built it in a way that allows additional warehouses to be added seamlessly. Great work!

The only take is that the total cost with that pattern is $19,772,076.15 (KLLILJJJ)  which is higher than 19,761,076 (LJIKLKJK)

[Malsadeh]

Thanks for bringing the Intelligent people on the table!