Hi all!
I'm try to create a workflow that sorts through duplicate retails in the same market to provide the correct retail in a new column called "Adj_Retail." I used a sum tool successfully to find the min and max for each market, but am having trouble getting a formula that tells it to pick the correct price.
For most markets, we go with the higher retail if the [% of market] is greater than 10, or [# of stores] is greater than 5. There are exceptions to this rule in certain markets as listed in the workflow, such that even a small amount of stores with a higher price means the Adj_Retail must be the higher price.
I tried tackling this two different ways:The first way was through individual formula tools, filters, and joins--seen at the top after the sum tool. The second was through sorting the data, adding a record count, then a multi-row formula tool to check each value compared to the last, then flipping it to cover from both sides--seen at the bottom. The latter does not have the specialty markets taken into account. Neither produced the results needed, but I feel like I'm close.
Ever stared at your code/project so long that it no longer makes sense and you need a second pair of eyes? That's where I'm at right now. Can you tell me what change needs to be made to get this where it needs to be?
*NOTE: This data is all proprietary and the exact names, markets, numbers, rules, etc are fictional.
