Allocate Adjustment based on groups

Question

Hi All,  I am trying to find solution to below. In below data set, I need to make adjustment of $7000 with logic as, if in given data set there are companies other then "Technology" then equally divide adjustment of $7000 to all those companies. In case there are only Technology companies then divide $7k between IBM, if IBM is also not there then divide between Microsoft and then Apple. List of Technology companies includes only these three but non tech companies can be many.

Any help will be highly appreciated, Thank You

Company IDCompany NameCompany Type256IBMTechnology256IBMTechnology158Microsoft

Technology158MicrosoftTechnology203AppleTechnology856CitibankBanking696JP MorganBanking784NikeConsumer268HSBCBanking

danilang · Answer

Hi @vjain17

The general approach would be something like this

1. Add a field [IsTech] that's either Y or N.

2. Count the number(N) of non-tech companies.

a. If its greater than zero, divide 7000 by N and assign this amount to all the non-tech companies using a join on IsTech

b. If equal to zero, count the number of IBM in Tech

1.if its greater than 0 assign a proportional amount to each IBM

2.if equal to 0 assign a proportional amount to each non-IBM tech company

Dan