Sum all possible combinations then match against a value

[Davonalt]

Hi

I hope the following make sense.

I hope the following makes sense. So I want to sum all the possible combinations the amount below, Per id then type.

id	Type	Amount
1	a	10
1	b	20
1	c	30
1	d	40
2	a	30
2	b	10
2	c	50
2	d	70

So something like:-

id	Combo	Sum amount Amount
1	a+b+c+d	100
1	a+b+c	60
1	a+b	30
1	a	10
1	a+d	50
1	b+c	50

An so on...

Then if the below matches with in 1 either way then return the combo’s that match

id	Amount
1	50.50
2	120.49

[TimN]

I saw this which solves a similar question.  Take a look here:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Finding-all-possible-combinati...

[rzdodson]

@Davonalt built a nested iterative macro approach for you. Was not exactly sure what you meant by the "Then if the below matches with in 1 either way then return the combo’s that match" statement. However, if you open the first iterative macro and go to Filter (21), I simply used the amounts from your last list to test whether each permutation from that ID group is less than or equal to its threshold for that group (ID 1 <=50.50).

Inner most layer of nested iterative macro.

Explanation: This layer iterates through the Iter field (Row 1, Row1 + Row2, Row 1+Row2+Row3), creates the permutation you are looking for in the Type field, and then sums the values from each letter. The output from this layer gives you all permutations of that Type group and their associated values that are summed.

First layer of nested iterative macro

Explanation: This layer iterates through each ID group, gets the values from all permutations, and then filters based on the your last table. If you need to change the threshold logic as mentioned above, the last Filter tool is where I would draw your attention.

Outer most layer of workflow

Explanation: Adding a RecordID tool establishes the critical Iter field we need to support the nested iterative macro setup.

Hope this gets you close to what you need! :)