Adjust highest record row based on another row

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

Datasets

Output

Transformation

Iterative Macro

Accepted answers

gabrielvilella

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

New Workflow4.yxmd

EricDavis

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.

New Workflow4.yxmd

All comments

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

New Workflow2.yxmd

gabrielvilella

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

New Workflow4.yxmd

EricDavis

New Workflow4.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335