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! :)

[Gunjan_Chhabria]

This is really great! I've applied this to a similar use case for myself as well. However it seems to be stopping after 100 iterations. I have 9.5k+ records across 400 odd distinct IDs. Only 3 of these IDs are being combined and the remaining which is 7.5k records is flowing in the the L output of the macro i.e., is left out.

Is there a threshold of  some sort within the macro that I should check - I don't see any

[abacon]

If it is an iterative macro, you can set the max iterations. Open up the macro, go to Interface Designer, under the settings icon. You will see max iterations.

Bacon

[Gunjan_Chhabria]

Hello, that does help I made the max limit as 20k, but now the macro takes about 3+ hours to run - is there a way to reduce time?

[Gunjan_Chhabria]

Additionally, is there a way to join row 1 and row 3 as well i.e., we are currently having a row for 

1

1,2

1,2,3

But is there a possibility to joing 1,3