Optimization for volumes with input parameters

[Tony_Tiger]

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

[AbhilashR]

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!

[Tony_Tiger]

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.