Hi Team,
I have following Amounts, which needs to be set off based on the combination of Amounts.
Input:
Expected Output:
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 @binu_acs @mceleavey @PhilipMannering
Lots of Optimisation questions on the community today@BRRLL99 here's my solution with the Alteryx toolYou only need 1 RHS as your only constraint is that the amount sums to 0Lmk if you have more questions
@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-zero/m-p/1267167/highlight/true#M316106
@BRRLL99 To learn more about the optimization tool, I'd recommend looking in the predictive tool sample workflows (see screenshot below)I'd also recommend the following articles:https://community.alteryx.com/t5/Tool-Mastery/Tool-Mastery-Optimization/ta-p/306117https://community.alteryx.com/t5/Maveryx-Success-Stories/Prescriptive-Analytics-Unleash-the-Optimization-Tool/ta-p/496332
https://help.alteryx.com/current/en/designer/tools/prescriptive/optimization-tool.html
Or just search 'Optimization' on the community.
Hope that helps,
Ollie
@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
in my original dataset,
the data is scrambled.
yes you are correct combinations of Amount should be zero.
@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.
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
@OllieClarke really cool stuff. Thanks for this.
@OllieClarke
Hi Ollie, for the following data
I have used your solution (without summarize option) for offsetting the amounts
when I did sum of "M" instead of zero it is giving me -0.10
as optimization tool S anchor will give offsetting amounts which will sum up to 0
Could please let me know if do i need to make any changes in the workflow , to make sure the M values are equal to 0
Hi @BRRLL99 Ah, I think I might know what is going on here. I've noticed that the Optimization tool sometimes doesn't behave as expected when given data with a very wide range. I think there's some level of rounding based on the largest value in the input set (although this is just a hypothesis). As your data spans 9 orders of magnitude I *think* that might be causing the issue. I would create a ticket with Alteryx Support as this limitation feels like a bug, or at least feels like it should be mentioned somewhere.Tagging @PhilipMannering as he's got a lot of experience with the tool. Have you confirmed this oddness?
