Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

list to combination of pairs

viveknarayananp
6 - Meteoroid

hi,
I am trying to split a list of values into pairs and have their "key" repeat.

 

I have a list of values, for eg.

key value
key1 a,b,c
key2 a,b
key3 b,c,d

 (could have mode than  3 values)

and want to get the output :

key1 a,b
key1 b,c
key1 a,c
key2 a,b
key3 b,c
key3 c,d
key3 b,d

 

Is there an easy way to do this ?

Thank you

5 REPLIES 5
jdunkerley79
ACE Emeritus
ACE Emeritus

Sure there is probably an easier way but this is do able via a text to columns to break it into rows on comma.

Then add an index column using MultiRow formula.

Create a next value and use this to join onto itself.

 

SelfJoin.jpg 

 

Attached as a workflow

michael_treadwell
ACE Emeritus
ACE Emeritus

I would consider using the R Tool to accomplish this.

 

There is a function in the utils package called combn that, given a vector (in this case the list of letters in [value]) and an integer (looks like you want 2 in this case), will generate all combinations of [value] taken 2 at a time.

viveknarayananp
6 - Meteoroid

jdunkerley79
With this it doesnt show all the possible combinations.
key1 b a
key2 b a
key3 c b
key1 c b
key3 d c

is the output and it missed out
key1 c a
key3 d b

but thanks for the idea.. a minor change might solve it.

jdunkerley79
ACE Emeritus
ACE Emeritus

Try this tweaked version.

 

Joins on self but only on key

Uses index to exclude self join]

Selects case where values in alphabetical order (i.e. a,b no b,a)

 

viveknarayananp
6 - Meteoroid

Thank you jdunkerley79
worked perfectly !

Labels