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 

[OllieClarke · Accepted solution]

@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/306117

https://community.alteryx.com/t5/Maveryx-Success-Stories/Prescriptive-Analytics-Unleash-the-Optimiza...

https://help.alteryx.com/current/en/designer/tools/prescriptive/optimization-tool.html

Or just search 'Optimization' on the community.

Hope that helps,

Ollie

[BS_THE_ANALYST]

@OllieClarke really cool stuff. Thanks for this.

[BRRLL99]

@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

[OllieClarke]

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?

[BRRLL99]

@OllieClarke @PhilipMannering 

As you said it might be true 
The above data set have count of 85, in this case M sum is not equal to Zero

I have randomly deleted few rows and make the count 79, in this case my M is equal  zero

[OllieClarke]

@BRRLL99 if I'm right the issue is not with the number of rows, but with the range of the values in those rows.

If I take your original sample and union it to itself 8 times, the Ms still sum to 0, even with 112 values.

However, in your later example, you've got some values which are in the order of magnitude 0.01 and others which are in 1,000,000 and that I think causes the issue.

[BRRLL99]

Is there anyway where I can bypass this limitation?

[BRRLL99]

If I try Min-Max scaling : according to you what should be the Fixed Range?

if  there is other approach to pass this limitation?

Please let me know

[MattLi]

Thank you, This is very helpful, I used optimization tool to achieve the same after reading your workflow.

May I kindly ask, if it is possible to apply a constraint that, if result has many possible combinations, pick the one, which rows in top got matched first and rows in bottom is left?

basically, I am doing this to automate a voucher matching function that try to match with FIFO rule.

Hope this makes sense.

Thanks