find which combination equals amount

[BRRLL99]

Hi Team,

I have following Amounts, which needs to be set off based on the combination of Amounts.

Input:

Amount
76.04
42.96
-119
70
30
-100
6
88

Expected Output:

Amount	Category
76.04	M
42.96	M
-119	M
70	M
30	M
-100	M
30
88

Solved: Re: Reconciling transactions (find which combinati... - Alteryx Community

I have tried to apply this solution, 
this is applying to Many to one combination ( M:1) only.

@BenMoss @binuacs @mceleavey @PhilipMannering 

[BS_THE_ANALYST]

@BRRLL99 the problem you're describing above isn't easy for the reader to decipher. 

It looks like you have some running totals and you're happy when they reconcile to 0? 

Difficult to say as you've said a lot without actually saying what the process is.

Hopefully the file I've attached provides some help.

All the best,

BS

[BRRLL99]

in my original dataset,

the data is scrambled.

yes you are correct combinations of Amount should be zero.

[BRRLL99]

@BS_THE_ANALYST 

This my original data

-2638800.26
-119
-100
-40
-13.40
2638853.66
-40
50
30
20
76.04
42.96
6
88

Expected Output:

-2638800.26	M
-119	M
-100	M
-40	M
-13.40	M
2638853.66	M
-40
50	M
30	M
20	M
76.04	M
42.96	M
6
88

I have seen your solution using python, i want the output in Alteryx
If not possible in Alteryx , Please give python solution

like this : Solved: Finding which values from the same column added to... - Alteryx Community

I tried different solution

but the it has only one rhs value

Solved: Finding combinations of numbers adding to a given ... - Alteryx Community

[OllieClarke · Accepted solution]

Lots of Optimisation questions on the community today

@BRRLL99 here's my solution with the Alteryx tool



You only need 1 RHS as your only constraint is that the amount sums to 0
Lmk if you have more questions

[BS_THE_ANALYST]

@OllieClarke I've not used the optimisation tool. I'll definitely check your solution out today. Very cool.

@BRRLL99 If I would have done this, I would have taken the absolute value of the entire column and stored that in a separate column and also created a flag if the number is positive or negative. You'd then be able to sort and group pairs of numbers together which can total 0.

I'd remove those entries via a record ID with a join for dynamic filtering. Then I'd be tempted to use python tool with an algorithm. I'd do this process iteratively. I'd likely select the largest positive number available and then see if there's any combination of negative numbers that can sum to reach the same magnitude of that number. If yes, remove that positive number and the combination of negative numbers from the list, if no, select the next largest positive number and repeat. Loop would stop when there's no positive numbers left. (This only takes care of one positive number with some combination of negatives so you'd then have to do opposite, largest negative number and combinations of positive numbers and work through the list of negative numbers).

May have to also consider combinations of positives and also combinations of negatives. Likely an iterative process here aswell.

There's probably some sort of algorithm to take care of the combination of numbers to make the target number.

All the best,

BS

[BRRLL99]

@OllieClarke Thank you for your help

I have same scenario with bit of different logic to offset Amounts.

in this case offset needs to be done based on group by of ID

Input:

ID	Amount
100A	-10
100A	-119
100A	8
100A	-10
100A	66
100A	76.4
100A	42.96
200A	-100
200A	77
200A	200
200A	-100
200A	-50
200A	-50
200A	66
200A	100

Expected: the first criteria to offset amounts should be done within the Group ID.

ID	Amount
100A	-10	M
100A	-119	M
100A	8
100A	-10	M
100A	66
100A	76.4	M
100A	42.96	M
200A	-100	M
200A	77
200A	200	M
200A	-100	M
200A	-50	M
200A	-50	M
200A	66
200A	100	M

[BS_THE_ANALYST]

I'd be tempted to batch macro it as I don't know if the optimisation tool does groupings. Worst case scenario, batch macro with Ollie's method

All the best,

BS

[OllieClarke · Accepted solution]

@BRRLL99 @BS_THE_ANALYST no need for a batch macro.

My post here is exactly the same use case, with the grouping. You just need to set up your constraints as I do

 https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Finding-amounts-that-sum-to-ze... 

[BRRLL99]

@OllieClarke 

Thank you It helped me to resolve my issue.

Please let me know where i can learn more about this tool?