Finding all possible combinations of each value

[richard_gardner]

I'm struggling with how to solve the following problem: I have a list of id's and values. The number of values associated to each id can be as low as one and as high as 24. I need to create a list of all possible combinations of values for each id.  Below is an example of the input and the expected output. Any suggestions on how I could solve this?

Input

id	Value
1	17
1	552
2	17
2	58
2	120
3	120

Expected Output

id	Combo 1	Combo 2	Combo 3	Combo 4	Combo 5	Combo 6	Combo 7
1	17	52	17, 552
2	17	58	120	17, 58	17, 120	58, 120	17, 58, 120
3	120

[data4accounting]

Hey @chukleswk - thanks so much for posting this. I was able to use this to solve my task. I had a question for you as I saw that you added a box in the main workflow named 'used in future to reduce iteration run.' I have a lot of data running through this macro and it looks like it is going to take a long time to process. I was curious how I could implement what you set up in this box to speed up the processing speed of the iterations? Thank you!!

[Hiblet]

I've got a macro on the Community pages that does not use iterations.  There is a nice solution to the Combinations problem that uses binary numbers.  If you want three things from eight, you can use all eight-bit binary numbers with three 1's in them to find all possible combinations very quickly, and you are guaranteed to find all of them.  This should be faster than looping, and also more robust, in that you cannot miss a combination due to a coding error.  Here is the macro link...

https://community.alteryx.com/t5/Community-Gallery/Combinations/ta-p/1023099

[data4accounting]

@Hiblet I did look into your solution but I don't think it produces what I'm looking for as it appeared to only allow you to pick combinations of a certain number but I wanted as follows: 

input: abc

output: a / b / c / a,b / a,c / b,c / a,b,c

[Hiblet]

Hi @data4accounting; OK, so, similar to the original poster.  If you did want to speed things up and avoid iteration, you just use multiple instances of the macro tool, and union the results. 

It looks like in your example above you want 1 from 3, 2 from 3 and 3 from 3.  For this, you would just have three instances of the macro, each taking a copy of the same input stream.  Each macro is configured to take 1, 2 and 3 from 3, and you union the results.  Not only would this be faster than iteration, it would work well with AMP, as Alteryx would work the streams in parallel. 

If you have an example data set that you can share, I would be happy to knock you up a demo.

Of course, you don't have to use the macro, but it seems that you are looking for performance, so it might be worth you trying it out to see if it helps. 

Either way, I hope you find a good solution.

[data4accounting]

@Hiblet - would this solution be scalable for x from x, as well? The issue I have is that the from 3 is not consistent but the iterative macro option is taking a really long time to run. I have attached the combinations I am looking to generate where Item Mapping ID 1 is my unique grouping identifier and then Row ID is the possible values for each grouping where the output is 2^# of Row IDs - 1. 

For example, the output for Item Mapping ID 1 is 2^2-1 or 3 combinations as follows: 1 / 2 / 1,2 

The output for Item Mapping 2 is 2^7-1 or 127 combinations as follows: 

1
2
3
4
5
6
7
1,2
1,3
1,4
1,5
1,6
1,7
2,3
2,4
2,5
2,6
2,7
3,4
3,5
3,6
3,7
4,5
4,6
4,7
5,6
5,7
6,7
1,2,3
1,2,4
1,2,5
1,2,6
1,2,7
1,3,4
1,3,5
1,3,6
1,3,7
1,4,5
1,4,6
1,4,7
1,5,6
1,5,7
1,6,7
2,3,4
2,3,5
2,3,6
2,3,7
2,4,5
2,4,6
2,4,7
2,5,6
2,5,7
2,6,7
3,4,5
3,4,6
3,4,7
3,5,6
3,5,7
3,6,7
4,5,6
4,5,7
4,6,7
5,6,7
1,2,3,4
1,2,3,5
1,2,3,6
1,2,3,7
1,2,4,5
1,2,4,6
1,2,4,7
1,2,5,6
1,2,5,7
1,2,6,7
1,3,4,5
1,3,4,6
1,3,4,7
1,3,5,6
1,3,5,7
1,3,6,7
1,4,5,6
1,4,5,7
1,4,6,7
1,5,6,7
2,3,4,5
2,3,4,6
2,3,4,7
2,3,5,6
2,3,5,7
2,3,6,7
2,4,5,6
2,4,5,7
2,4,6,7
2,5,6,7
3,4,5,6
3,4,5,7
3,4,6,7
3,5,6,7
4,5,6,7
1,2,3,4,5
1,2,3,4,6
1,2,3,4,7
1,2,3,5,6
1,2,3,5,7
1,2,3,6,7
1,2,4,5,6
1,2,4,5,7
1,2,4,6,7
1,2,5,6,7
1,3,4,5,6
1,3,4,5,7
1,3,4,6,7
1,3,5,6,7
1,4,5,6,7
2,3,4,5,6
2,3,4,5,7
2,3,4,6,7
2,3,5,6,7
2,4,5,6,7
3,4,5,6,7
1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,6,7
1,2,3,5,6,7
1,2,4,5,6,7
1,3,4,5,6,7
2,3,4,5,6,7
1,2,3,4,5,6,7

As seen, some of these get quite large so any help to optimize speed would be appreciated!

[data4accounting]

Hi @Hiblet - is this scalable to x from x combinations? The issue I have is that the from 3 is not consistent. I have attached all example combinations where Item Mapping 1 ID is the unique combination identifier and the Row ID is the number of possible values where 2^# of Row IDs -1 is the total combinations. For example, Item Mapping 1 ID = 1 should output 1 / 2 / 1,2 but then Item Mapping 1 ID = 2 has 7 different values so the output for that one is 127 different combinations and some of these get quite large. 

[Hiblet]

Hi @data4accounting , the problem here is really that Combinations scale factorially.  In your Spreadsheet, you have group 19 that has 888 records.  The number of possible combinations for 888 items is approx 2.6 x 10e1741, ie mind-boggling huge.  For perspective, it is estimated by boffins that the number of atoms in the observable universe might be around 10e82, a 1 with 82 zeros after it.  10e1741 is a 1 with 1741 noughts after it.  We may have to wait for quantum computers to work this stuff out, but don't hold your breath.

Just having 12 items gives you 6.2 billion possible combinations.

Is it possible to restrict your problem domain to smaller groups?

[data4accounting]

@Hiblet - yes I realized that. I was actually able to tailor your macro and use it for all combinations because there is just a filter in the middle doing the pick selection but as your mentioned, my combinations are just too much to process so I'm trying to figure out how we can truncate the population. 

[Hiblet]

Hi @data4accounting Great, definitely get under the hood and chew that macro apart.  What is the background to the problem?  It could be that this is a packing problem and "quite good" might be good enough.  There are a number of strategies for approximating solutions or making good guesses and improving them.  Can you share more about what you are trying to achieve in the end?