Adjust Row Values of one column with Adjacent Columns

[Tej_Singh]

For each row having a minus value under column Minus we need to adjust that minus value among adjacent columns

INPUT

Sum_A	Minus	Sum_A Revised	Sum_1	Sum_2	Sum_3	Sum_4	Sum1_Sum4
100	-50	50	20	40	20	20	100
50		50	20		30		50
40	-10	30	5	10	5	10	40
100		100		50		50	100
30		30			30		30
75	-25	50		25	25	25	75

Problem Statement: For all rows that have a minus value we need to adjust Minus value in all Columns from Sum_1 to Sum_4 such that Sum1_Sum4 = Sum_A Revised

OUTPUT

Sum_A	Minus	Sum_A Revised	Sum_1	Sum_2	Sum_3	Sum_4	Sum1_Sum4
100	-50	50	0	10	20	20	50
50		50	20		30		50
40	-10	30	0	5	20	5	30
100		100		50		50	100
30		30			30		30
75	-25	50		0	25	25	50

Is there a way we can achieve this in Alteryx?

[Nanoq]

Hello @Tej_Singh  we definetley can!
but whats the logic of what column that has to be reduced?

[pdave87]

@Tej_Singh Let us know if below approach works for you. Same workflow attached for your test run.

[Tej_Singh]

thanks @pdave87  However; you have a partial solution. You are just amending column Sum1_Sum4 to match column Sum_A Revised. You need to amend values at individual columns Sum1 to Sum_4 as well so that their sum also equals to Column Sum1_Sum4.

Can you try that as well?

[Tej_Singh]

Logic will go this way. For row with value under Minus column, we will first add Minus Value to Col Sum_1. If the outcome of Sum_1+ Minus < 0 then Sum_1 =0 and reminder Minus value of Sum_1+ Minus will be carried forward.

For example in Row 1 first we will add -50 to sum_1 value 20 (20-50 = -30) Since less than zero sum_1 will be changed to 0 and we will then add -30 to Sum_2 value which is 40. (40-30=10) Since result is positive we will change Sum_2 col value to 10 and we can stop here as now sum of individual columns Sum_1 to Sum_4 will be equal to Sum_Revised A column.

Any thoughts if this can be achieved by any ways in Alteryx?

[atcodedog05 · Accepted solution]

Hi @Tej_Singh 

Here is how you can do it.

Workflow:

Hope this helps : )

[Tej_Singh]

Thanks @atcodedog05 . Excellent solution. It is working fine! Only there was a slight change required to the Multi-row formula

In your workflows it is

ELSEIF [RunTot_Value]<-1*[Minus]

ideally it should be

ELSEIF [RunTot_Value]<=-1*[Minus]

Thanks once again! 🙂

[atcodedog05]

Happy to help : ) @Tej_Singh 

Cheers and have a nice day!