Adjust Row Values of one column with Adjacent Columns

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?

Data_Example.xlsx

Developer

Developer Tools

Preparation

Data Investigation

Accepted answers

atcodedog05

Hi @Tej_Singh

Here is how you can do it.

Workflow:

Hope this helps : )

SumLogic.yxmd

All comments

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.

SumLogic.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703