Matrix of combinations

[TheOrchyster54]

Hi everyone,

I was wondering if anyone had any idea on how to generate a matrix where:

- 1 row is a combination

- 1 column per element possible of a combination

My input is p lists as follows:

List 1	List 2		List p
Bob	Blue		Paris
Katie	Red		London
Karen			Madrid

1 combination being one element of list 1, one element of list 2 & one element of list p. For example, "Bob, Blue, Paris" or "Bob, Blue, London" or "Karen, Red, Madrid".

If n_p is the length of the list. The matrix will give n_1*n_2*...*n_p rows and n_1+n_2+...+n_p columns

The output wished would be:

Combination	Bob	Katie	Karen	Blue	Red	...	Paris	London	Madrid
#1	1	0	0	1	0	...	1	0	0
#2	1	0	0	1	0	...	0	1	0
	...	...	...	...	...	...	...	...	...
#n_1*n_2*...*n_p	0	0	1	0	1	...	0	0	1

Thank you very much for your help,

Jean

[danilang · Accepted solution]

Hi @TheOrchyster54 

Tricky little problem.  To solve it, use an iterative macro to generate all the combinations and then split and crosstab.  The first transpose makes it dynamic to handle any number of lists and elements per list

Main

Macro

Dan

[PhilipMannering · Accepted solution]

Secondary to @danilang , here's another solution,

[TheOrchyster54]

Wow great, thank you very much both of you! 

Still starting with Alteryx, didn't know we could embed Python code, it's wonderful :o!

I thought I was forced to do it in 2 steps, output in .csv, open Jupyter NoteBook and then go again in Alteryx!