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?
Solved! Go to Solution.
Hello @Tej_Singh we definetley can!
but whats the logic of what column that has to be reduced?
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?
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?
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! 🙂
Happy to help : ) @Tej_Singh
Cheers and have a nice day!