This content has been marked as final. Show 6 replies
I'm assuming you're confortable working out the score for each quote, and it's just the ranking part you're unsure about?
While not exactly the same as your scenario, there are several forum threads which discuss ranking, prioritisation and allocation, which may be helpful. I think you will get some good ideas from these threads:
* New Tutorial and example available on OTN - representing preferences: New Tutorial and example available on OTN - representing preferences
* Can entities be sorted: Re: Can Entites be sorted
* Assigning identifiers automatically or using the base value in rules: Assigning identifiers automatically or using the base value in rules
* Calculating the least cost of a product across stores useing OPA: Calculating least cost of a product across stores using OPA
* How to show logic of picking multiple stores to fulfill an order line item: Re: How to show logic of picking multiple stores to fulfill an order line item
As you will see from these discussions, the key to any ranking problem is inferred relationships!
From what I see in your initial description I'm guessing that the entity structure for the containment relationships would be something like this:
Global -> one-to-many -> the supplier
the supplier -> one-to-many -> the supplier's quote
You will also need inferred relationships, probably something like the following:
the supplier's quote -> many-to-many -> the supplier's quote
the supplier -> many-to-many -> the supplier
The purpose of the inferred relationships is so you can do logic between different instances of the same entity. For example, if a supplier submits 3 different quotes for the same piece of work, you need to work out which of the 3 quotes is the 'best' according to whatever your scoring method is. After this, you then need to work out which supplier is the best (based on each supplier's respective best quote).
Note that inferred relationships are the way to approach this type of logic problem if you need to have some sort of ranking. If you just need to work out the 'best' and you don't care about the rest, then it's a simpler problem and you may not need inferred relationships at all. For example, if each quote gets an independent score and you just need to find the highest-scoring quote for each supplier, then you can use regular entity functions such as InstanceMaximum and InstanceMaximumIf. Same goes for the suppliers if you only need to find the supplier with the 'best' overall score.
I have defined the containment relationships and it is identical to what you have listed below.
My scoring/weightings and inferred relationships are all functioning and I am able to assign a score to each quote that is made by a supplier.
I am using the InstanceMaximumIf function to find the supplier with the best score. I also need to to rank all the quotes. I.e provide a rank from 1-50 for the all the 50 quotes based on the score that has been given to each quote.
I am getting stuck on assigning a rank to all of each of the quotes score. Is there a simple solution to this that is escaping me?
Use InstanceCountIf to count the number of instances with a higher score and add 1 to get the rank(i.e. the "rank" is 1 if 0 are higher, 3 if 2 are higher, etc.) You need to consider how to rank ties (i.e. should scores of 100, 90, 90, 85 result in ranks of 1, 2, 2 and 4 or is there some additional tiebreaker to be used to create an absolute rank).
See the function reference in http://docs.oracle.com/html/E38269_01/toc.htm for more info on InstanceCountIf