Adjust highest record row based on another row

[EricDavis]

Hello,

Raw Data:
Field 1	Field 2	Field 3	Amount
Eric	Davis	LA	15
Emma	Davis	LA	-80
Hannah	Davis	LA	-10
Emma	Case	LA	40
Emma	Evans	LA	50

I have the above raw data sample (millions of lines) and I need to adjust the highest "Amount" field based on other records with matching columns (in this example only "Field 1"/"Field 3"). The output would need to show the in/out of adjustment.

I did this in Microsoft ACCESS through a max query and for loop iteration. How would I achieve this in Alteryx?

Sample output (Hannah had no other positive "Amount" matching records to offset, so not included)

Output:
Field 1	Field 2	Field 3	Amount
Emma	Davis	LA	80
Emma	Case	LA	-30
Emma	Evans	LA	-50

[gabrielvilella]

Why do you have Emma Case LA -30? Shouldn't it be -40? Also, if this is true, I don't quite get what you are doing here, for me you are just multiplying the numbers by -1.

[EricDavis]

I have to re-allocate the -80 for Emma/Davis/LA to offset other records.

The highest matching for Emma/LA is line 5 with total of 50. so need to adjust that line by -50

Remaining balance is -30.

Next highest matching is line 4 with 40. So need to adjust that line by -30.

No more negatives for Emma/LA so no more adjusting needed.

Hannah/LA doesn't have any matching positive records so no adjustments to be made.

The output shows the change/movement that is occurring (which is needed to book a journal entry).

The final table after adjustment would look like:

Field 1	Field 2	Field 3	Amount
Eric	Davis	LA	15
Emma	Davis	LA	0
Hannah	Davis	LA	-10
Emma	Case	LA	10
Emma	Evans	LA	0

[EricDavis]

I've attached a workflow that has the example in it. I did the sort, find highest and joined them together line by line. Which can then be broken back up. However, once i allocate the negative to first line, i need the next line to show the updated balance for allocation (in a full dataset there could be more than 2 lines that are adjusted with the same Field 1/Field 3 matches, so couldn't get the multi-row tool to work for myself).

[gabrielvilella · Accepted solution]

Maybe this is what you are looking for. I just don't know how you get to that last table you posted.

[EricDavis · Accepted solution]

Thank you for your help! Using this i was able to make the attached that solved the situation. Your workflow worked for up to 1 row duplication and didn't get the adjustment. Looks like i needed to do get two fields updated within the multi row tool. Not elegant, but i did a concatenated field to get remaining/adjustment balance and then delimited later when making the output.