Adjust amount vertically within program

JJ523

Hi There, I tried all sort of methods to get solution for adjusting the negative amounts with the highest amount within the program. But couldn't be lucky to get to the solution. Could you someone help me out on this. Attached is the screenshot of Input data and output data for reference. Appreciate for your help.

e.g.: -63 amount to be adjusted with 120 within A program.

Program	Event	Amount		>>>		Program	Event	Amount	Status
A	A1	120				A	A1	57
A	A2	35				A	A2	35
A	A3	-63				A	A3	-63	Adjusted
B	B1	78				B	B1	78
B	B3	-96				B	B2	-96	Not Adjusted

atcodedog05

Hi @JJ523 

Here is how you can do it.

Workflow:

1. Using sort tool to sort by program and the by amount.

2. Using record id to set a ID for row.

3. Using summarize tool to find the lowest value (-ve) and lowest record id (first highest value).

4. Using filter to keep the min values only if its negative.

5. Using join multiple tool to do outer join on record id.

6. Using formula tool to check and flag whether it can be adjusted. If yes make the adjustment.

Hope this helps : )

adjust value.yxmd