Permutations are ways to order items. It is simple to work out how many permutations there are for a set of items, but not so simple to list out those permutations.
As the number of items increases, the complexity scales enormously. For 10 items, there are 10 raised to the power of 10 possible permutations ie 10 000 000 000.
For the letters ABC, if you pick 3 letters, you have six possible permutations...
ABC
ACB
BAC
BCA
CAB
CBA
If you include picking 1, 2 or 3 items, you have 15 possible permutations...
A AB ABC
B AC ACB
C BA BAC
BC BCA
CA CAB
CB CBA
This macro allows you to input 9 or fewer string items, and list out the possible unique permutations of those items. You can choose how many items to pick from a possible 9. The ABC example above would be generated if you input 3 records with A, B and C as strings, and chose 1 and 3 as your min and max pick limits.
The macro will accept multiple character strings, not just single characters. This means you can workout permutations where characters or numbers have to stick together. For example, if you want to permute Snap, Crackle and POP all possible ways, you would get...
Snap SnapCrackle SnapCracklePOP
Crackle SnapPOP SnapPOPCrackle
POP CrackleSnap CrackleSnapPOP
CracklePOP CracklePOPSnap
POPSnap POPSnapCrackle
POPCrackle POPCrackleSnap
Output is provided in 3 formats...
- S: A composite string like ABC
- C: A comma separated list like A,B,C
- R: Rows with order labels, so you can composite the results however you want.
There is a workflow included in the package that demonstrates use-cases for the macro, called Permutant_1_Harness.yxmd. This shows how to push input to the macro, and what comes out.
How It Works: The macro uses pre-computed lookup tables, called Base...yxdb . These are all the permuation results for 3 to 9 characters, built using numeric digits. The user's input strings are then substituted for the numeric digits. This speeds up permutation calculation, so that for 9 items, calculation takes approx 10 seconds. If you move the macro around, you will need to keep the Base*.yxdb files with the macro for it to work correctly.
Note: There is a minimum input number of 3 items, as the ways to permute 2 items are trivial ie for AB, you can have A, B, AB and BA only. There is a max of 9 items, as permuting 10 items is astonishingly resource intensive, which means my poor old laptop could not generate the lookup tables.
This macro would be good for Scrabble players; Input your 7 letters, and you could work out all possible 1 to 7 letter words. If you want to lay your tiles on the board around another letter, you can include that as the eighth. Just don't get caught cheating.
If you need a strong technical partner to help achieve solutions with Alteryx, please check out Continuum Jersey (www dot continuum dot je). This is a very innovative and forward thinking consultancy, specialising in the financial sector. We would love to work with you to get the most out of Alteryx for your business.
