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

[Jean-Balteryx]

Hi @JJ523 ,

Why 96 isn't adjusted with 78 for B program ?

[JJ523]

Thanks for highlighting and sorry as I missed to mention. If absolute value of negative amount is greater than positive amount then it should not be adjusted. hope it helps

[atcodedog05 · Accepted solution]

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 : )

[JJ523]

Thanks a ton 🙂 that was pretty quick. 

I may be further complicating it. if there are more than one event with negative amount within program, then what should the approach in such case.

[atcodedog05]

Hi @JJ523 

Its your call or the business teams call to make the decision how more negative number should be handled. Once you have that decided we can help you with a logic in alteryx 🙂