Picking combinations of k items from a list of n items is a classic computing problem. Usually it is solved by repeated iterative loops, which can lead to odd bugs and speed problems.
Researchers at MIT have shown that there is commonality between the combination problem, and how binary numbers are represented. We can use this relationship in Alteryx to make a non-iterative macro to output all required combinations.
The input expects string RecordIDs in one column, and the integer number of records you want to select into unique combinations from this set. The int number is only taken from the first record in the set, and repeating down the whole column, or changing it mid column, has no effect.
For example, input would look like this...
RecordID,Picks
A,2
B,2
C,2
D,2
This would ask the macro to produce all combinations of 2 records, from the input set of 4 records (trivial, good for an example only).
The output is three columns. First column is the ID sequence number for the combination. The second number is the sequence number within each combination. The final column is the recordID for this combination, at this sequence.
For the input example above, the output looks like this....
Combination,Pick,RecordID
1,1,A
1,2,B
2,1,A
2,2,C
3,1,A
3,2,D
4,1,B
4,2,C
5,1,B
5,2,D
6,1,C
6,2,D
So, combination 1 is "A,B", combination 2 is "A,C", etc.
You can turn this into one row per combination very easily using a subsequent Crosstab tool. Group on combination, using Pick as the column and RecordID as the value.
Warning - using many records will result in a lot of memory being used. Use the well known maths to work out how many combinations should exist (Google "calculate number of combinations of k from n") and if you are up around the millions, cache the output and be prepared to wait for your answer.
Refs:
Post from jacoblambert on this thread... https://stackoverflow.com/questions/127704/algorithm-to-return-all-combinations-of-k-elements-from-n...
