Hi
I need to balance positive and negative Amounts where sum is equal to zero.
for Example :
Input :
row | Amount |
1 | 101.05 |
2 | -50.45 |
3 | -50.6 |
4 | 30.99 |
5 | 40.67 |
6 | 67.78 |
7 | -30.55 |
8 | -44.33 |
9 | -33.57 |
10 | 60.77 |
Expected output :
row | Amount | type |
1 | 101.05 | balanced |
2 | -50.45 | balanced |
3 | -50.6 | balanced |
4 | 30.99 | |
5 | 40.67 | balanced |
6 | 67.78 | balanced |
7 | -30.55 | balanced |
8 | -44.33 | balanced |
9 | -33.57 | balanced |
10 | 60.77 |
As shown in the expected output, Sum of balanced column is equal to zero
As shown in the expected output from row 1 to 2 sum is zero ( balance is done based on 1 : M combination , 1 positive and M negatives)
and from row 5 to 9 sum is zero ( balance is done based on M : M combination , Many positives and Many negatives)
I need to build a workflow which balances amount with combination
expected output should be tested with data set -1 and data set -2 excel file
is it possible with python tool in Alteryx ?
Solved! Go to Solution.
You can do this through Python of course, but here is an Alteryx only solution using an iterative macro. Basic idea is start at the top of the file and get a running total. Check for the first row where running total is 0. If no rows found go to the next row. Else flag the set as balanced and go to 1 + the last row number.
@Divyajyothi7 attaching the python version
I have tried your logic for the attached data set. Results are not as expected, every row of balanced column showing as null()
could you please check with the attached 2 datasets
thank you
your code is working with dataset and giving expected results.
is it possible to get expected results without python ?
@Divyajyothi7 sorry for the oversight. With your data set we were getting floating point rounding errors in my original solution. Attached solution uses the fixed decimal data type to fix this and should produce the results you need.
with sample data we are getting expected results.
but with data in excel, we are not getting expected results
could you please check ??
@Divyajyothi7 I don't see any sets that should return balanced from your data set unless the sequence of rows does not matter. For example can row 3 and row 9 balance each other or do the balancing rows have to be consecutive?
@griffinwelsh
I have checked your workflow with this data, no amounts are balancing
As you can see the following Amounts are balanced with this data
could you please check ??
original dataset amounts column will be sorted in asc order
Amount | type |
-751228.5 | |
-68039.4 | |
-52478.9 | |
-43102.3 | |
-31487.8 | |
-24178.3 | |
-16844.4 | |
-9489.4 | |
-3641.7 | |
-2766.3 | |
32508.03 | |
1236972.49 | |
-116000 | Balanced |
-49125 | Balanced |
-37948.84 | Balanced |
-18792.43 | Balanced |
-6667 | Balanced |
-3587.84 | Balanced |
10254.84 | Balanced |
67917.43 | Balanced |
153948.84 | Balanced |
Sorry for the confusion, but I thought you were asking to consider only adjacent rows for balancing. When considering all possible combinations I won't be able to build anything nearly as efficient as Binaucs solution in python. I recommend you go that route.