I'm new to Alteryx designer, I want to optimize volume and cost with a parameter input file. I'm looking for an easy workflow to select carriers and lanes based on volume restrictions. I have over 100k rows in an excel sheet. Below is only a mock up of a small sample size.
1) Workflow for volume
2) Workflow for Cost
3) if possible, a workflow to combine volume and cost
Sample from excel
Lane ID | Carrier | Carrier Rank based on cost | Volume | Historical Cost | New Cost |
1 | A | 1 | 11 | $ 1.05 | $ 1.00 |
1 | B | 2 | 11 | $ 1.05 | $ 1.01 |
1 | C | 3 | 11 | $ 1.05 | $ 1.02 |
2 | C | 1 | 4 | $ 2.07 | $ 2.00 |
2 | B | 2 | 4 | $ 2.07 | $ 2.01 |
2 | A | 3 | 4 | $ 2.07 | $ 2.02 |
3 | C | 1 | 5 | $ 2.99 | $ 3.00 |
3 | A | 2 | 5 | $ 2.99 | $ 3.01 |
3 | B | 3 | 5 | $ 2.99 | $ 4.02 |
4 | C | 1 | 7 | $ 5.00 | $ 4.00 |
4 | A | 2 | 7 | $ 5.00 | $ 4.01 |
4 | B | 3 | 7 | $ 5.00 | $ 4.02 |
5 | B | 1 | 18 | $ 3.99 | $ 5.00 |
5 | C | 2 | 18 | $ 3.99 | $ 5.01 |
5 | A | 3 | 18 | $ 3.99 | $ 5.02 |
Parameter for volume
Carrier | Volume | Min | Max |
A | 29 | >1 | <=29 |
B | 12 | >1 | <=12 |
C | 4 | >1 | <=4 |
Parameter for Cost
Carrier | Cost |
A | Lowest |
B | Lowest |
C | Lowest |
Lane ID | Volume | Max |
1 | 11 | Full Amount |
2 | 4 | Full Amount |
3 | 5 | Full Amount |
4 | 7 | Full Amount |
5 | 18 | Full Amount |
Hi @Tony_Tiger, I wasn't 100% clear on what your end goal would look like, so for now I have mocked up a sample solution based on my interpretation.
For Volume, I approached it like an excel lookup problem. I have used the Alteryx Join in combination with Formula tool to identify Carriers that fall within a particular range.
For Cost, I am assuming you are looking for the lowest Carrier across the different LaneID's, and so I have used the Summarize tool to identify the lowest minimum cost transaction for a given Carrier and joined it back to the main data. The formula tool helps notate if a particular record is the lowest for the Given Carrier or not.
I hope this helps!
Hi AbhilashR
Thank you for getting me starting to think how my workflows should look like.
Below is how I want the output for volume:
Volume output for Carrier A
Lane ID | Carrier | Carrier Rank based on cost | Volume | Historical Cost | New Cost |
1 | A | 1 | 11 | $ 1.05 | $ 1.00 |
5 | A | 3 | 18 | $ 3.99 | $ 5.02 |
Total | 29 |
Volume output for Carrier B
Lane ID | Carrier | Carrier Rank based on cost | Volume | Historical Cost | New Cost |
4 | B | 3 | 7 | $ 5.00 | $ 4.02 |
3 | B | 3 | 5 | $ 2.99 | $ 4.02 |
Total | 12 |
Volume output for Carrier C
Lane ID | Carrier | Carrier Rank based on cost | Volume | Historical Cost | New Cost |
2 | C | 1 | 4 | $ 2.07 | $ 2.00 |
total | 4 |
I was wondering if we can combine the volume and cost workflows together? Lowest cost with particular volume range for each carrier. Above tables are only 1 combination to get volume qualify the ranges, but it isn't the best scenario for the lowest cost.