|
benking9987
Posting Yak Master
124 Posts |
 Posted - 2011-04-14 : 14:35:57
|
| I have a table containing sales information for multiple items. I have thousands of items that are sold to one or more customers. The layout of the table looks something like this:Item1, Customer1, MarginCustomer1, [Rank] (this is what I need)Item1, Customer2, MarginCustomer2, [Rank]Item1, Customer3, MarginCustomer3, [Rank]Item1, Customer4, MarginCustomer4, [Rank]Item2, Customer1, MarginCustomer1, [Rank]Item2, Customer4, MarginCustomer4, [Rank]Item3, Customer2, MarginCustomer2, [Rank]Item3, Customer3, MarginCustomer3, [Rank]Item3, Customer4, MarginCustomer4, [Rank]Item4, Customer3, MarginCustomer3, [Rank]Since not every item is sold to every customer, there are not always the same number of customer listings per sku. What i ultimately need to arrive at is being able to rank each Item/Customer pair. The top ranking Customer for each item will have the highest MarginCustomer, then ordered descending thereafter. These ranks will then be used to help me determine priorities The company is limited to these 4 customers for now. Since the sales data (margin amounts) are always changing, i need a way to dynamically generate these rankings to use within the rest of my allocation regime.Some sample data might look like this:Item1, Customer1, .375Item1, Customer2, .321Item1, Customer3, .102Item1, Customer4, .412In this case, the ranking I would need to generate and write back to the table would look like this:Item, Customer, Margin, RankItem1, Customer1, .375, 2Item1, Customer2, .321, 3Item1, Customer3, .102, 4Item1, Customer4, .412, 1Anyone out there who could help me with this?Thanks a million in advance. |
|
Ranking records