get all possible combinations of column values

[iperich]

Hi gurus...

I have to do some simple task but I don't know what to do... I need to get all combination (not permutation) of 2 values given a "group id" value

I have this

group id	value
1	a
1	b
1	c
2	b
2	c
2	d

And I want to have this

group_id	combination
1	a b
1	a c
1	b c
2	b c
2	b d
2	c d

I have to do this combinating 2 values by now, but it probably will be required combinate 3 values. I don't have a clue what to do, I don't even know what labels put to this post.... lol

any help?

Thanks in advice...

[JohnJPS]

One thing you could try is select the variables in question and send them into the R tool, then use expand.grid() on that subset of variables.

[SeanAdams · Accepted solution]

another alternative is to do a self-join.   Join this data-set to itself using the GroupID field.

I've attached a worked example to this post - with two legs.  One includes duplicate combinations (like aa; bb etc), and the other filters these out.

If this solves the problem, would you mind marking this as solved - or if you have further questions, then it may be worth attaching a sample workflow with a mocked up data-set so that the community can help you out by completing the workflow.

Thanks @iperich

Sean

[iperich]

Self join did the trick. And if I want to mix 3 fields, I can combine the 3rd with the mix of the other two, checking that the combination of the first two doesn't contain the 3rd one.... Great!!

Nice one, Sean, thank you very much!

[KK17]

Hi @SeanAdams,

This solution works great with a project am working on as well.

But on top this, I have an additional requirement which although sounds simple is becoming cumbersome to achieve the required results.

To explain, lets consider the same example you mentioned the solution to. My requirement is to create a combination of the group-id along with the combinations of the values (screenshot attached).

Any idea how this can be achieved?

Thanks in advance!

[JoeS]

Hi @KK17,

I think I have solved your example using the append fields tool and connecting the original data up twice so you end up with all combinations. 

Then you can use a formula tool to combine the two different values for your new group and new value fields.

Please see the attached workflow.

[KK17]

Hi @JoeS,

Works perfectly fine and the issue is solved. Thanks a lot!

For some reason, I was grouping my data from 2 columns and then feeding in to the append tool which essentially was eliminating half of data. Silly me!

Thanks again! Cheers!