Qlikview Bid Analysis Tool - Choose combination of X suppliers offering greatest savings

[#1]

I’m trying to build an application which evaluates the results of a transportation bid.  One scenario that I’m struggling with is the following:

Choose the combination of 3 suppliers which offers the largest overall savings.

I can easily identify the 3 suppliers that individually offer the greatest savings, but I’m struggling to figure out how to identify the combination of three suppliers which offers the greatest savings.  For example.

[Lane,Supplier,Savings

1,A,17

1,B,15

1,C,5

1,D,5

2,A,2

2,B,3

2,C,5

2,D,1]

From an individual perspective, supplier A offers the most savings at $19, B offers the second most at $18.

However, the combination of two suppliers offering the greatest savings is A and C at $22.  A and B only offers $20. 

Any thoughts on how I might do this?

[mark_casselman]

Hi,

Of course with only 2 lanes you can only have a combination of maximum 2 suppliers (you asked for a combination of 3 vendors). So I added 2 more lanes.

A good way is to generate all possible combinations of 3 or less suppliers and calculate their best total saving. I tried to do it in the dashboard, but it seems much easier to do it in the script.

Script:

Savings:

LOAD * Inline [

Lane,Supplier,Savings

1,A,17

1,B,15

1,C,5

1,D,5

2,A,2

2,B,3

2,C,5

2,D,1

3,A,2

3,B,2

3,C,2

3,D,2

4,A,3

4,B,3

4,C,3

4,D,3

];

Combi:

LOAD Lane, Supplier as Supplier1, Savings as Savings1 resident Savings;

left join (Combi)

LOAD Lane, Supplier as Supplier2, Savings as Savings2 resident Savings;

left join (Combi)

LOAD Lane, Supplier as Supplier3, Savings as Savings3 resident Savings;

NoConcatenate

CombiMax:

LOAD *

, if(Savings1>Savings2,if(Savings1>Savings3,Savings1,Savings3),if(Savings2>Savings3,Savings2,Savings3)) as MaxSavings

, if(Savings1>Savings2,if(Savings1>Savings3,Supplier1,Supplier3),if(Savings2>Savings3,Supplier2,Supplier3)) as MaxSavingsSupplier

resident Combi

where Supplier1<=Supplier2 and Supplier2<=Supplier3;

drop table Combi;

And make a Straight table wit dimensions Supplier1, Supplier2, Supplier3 and an expression sum(Savings) and sort it descending on the sum(Savings).

On top you wil find the best combinations (but you also have the result for all other combinations wich might help in negotiations !).

The result:

Ok ?

[#3]

Thank you for the response Mark.  I provided the dataset in my initial posting as a way of illustrating what I am trying to do.  In reality I have over 100 suppliers.  Any thoughts on how I might scale this solution?

[#4]

To clarify.  I obviously don't want to be able to see a scenario with 100 suppliers involved, but 10-20 isn't out of the question.  Also, I'd ideally like to be able to adjust the number of suppliers included within the application.

[mark_casselman]

Hi Bradley,

I liked your 'problem' and made a more generic solution. Still generating all combinations inb the script.

You can change the number of lanes, the number of suppliers. The maximum number of suppliers in the combination is set to 5.

All combinations are calculated, double combinations are removed and you even get the list of wich lane by wich supplier.

You can filter on the number of suppliers in the application, but be carefull, is you choose 3 for example, it could be that there is a better combination of ony 2 suppliers ! So always check the complete list...

Watch out, using 10 lanes, 10 suppliers and 5 combinations generates 1 million temporary rows  in the load script, no problem for QlikView, but 10,10,10 would generate 100 billion rows...

Mark

[#6]

Mark, thank you for another useful suggestions.  I will definitely get some use out of this script in the future.  However, for my current challenge, the data set is simply to large to be run through this script.  I'm beginning to think that Qlikview may not be the right tool in this particular instance.