Finding all possible combinations of each value

[richard_gardner]

I'm struggling with how to solve the following problem: I have a list of id's and values. The number of values associated to each id can be as low as one and as high as 24. I need to create a list of all possible combinations of values for each id.  Below is an example of the input and the expected output. Any suggestions on how I could solve this?

Input

id	Value
1	17
1	552
2	17
2	58
2	120
3	120

Expected Output

id	Combo 1	Combo 2	Combo 3	Combo 4	Combo 5	Combo 6	Combo 7
1	17	52	17, 552
2	17	58	120	17, 58	17, 120	58, 120	17, 58, 120
3	120

[chukleswk]

This gets you part of the way there....

I have come up with the following solution. There may be a better / faster way to do it....but this is what I came up with.

The top leg finds out how many numbers are in that ID and then adds 1. This is because you are wanting to start any combinations for that group in a combo row that is 1 greater than the total number of records in that ID.

The bottom leg creates a new field that will be used to help every record in that id to be in it's own combo column.

You then join those together and do a cross tab grouping it on the id. It is important to note that in order to get the combination you will need to change your Value to a string, otherwise it will try and do math on it when you do the cross tab.

 

[richard_gardner]

This is close, but I don't get all possible combinations. For example, id 2 should have also the following combinations:

17, 58

17, 120

58, 120

[chukleswk]

So, I have it working if you have up to 3....when you go beyond that, then it get's a lot more difficult and is something I'm going to have to think on and work through.

I guess my question for you @richard_gardner, are you looking to do an max number of entries per id or is this something that could be infinitesimal?

 

[richard_gardner]

Hi @chukleswk 

The number of entries could be higher in a future dataset, but the max number of entries for any id in my current dataset is 28.

[chukleswk]

I’ll keep looking at it…..but right now, the max I can get you (good) right now is 3.

[LiuZhang · Accepted solution]

Here is my attempt, I used iterative macro for combination. Still need to wrap a batch outside to handle your case, but hopefully that's the easier part.

[chukleswk]

@LiuZhang's will definitely work. I was able to run through 10 iterations of 28 variables and it took roughly 35 minutes. This was able to 24.8 million different combinations. With having upwards of 28 different iterations, you're going to have upwards of 268.4 million combinations for a single ID Grouping. If you have to run this on an unspecified number of groupings it could take you quite a while using Alteryx. It may be better to run it in Python or C: https://www.geeksforgeeks.org/print-all-possible-combinations-of-r-elements-in-a-given-array-of-size... 

@LiuZhang One of the things I did notice is that this works great as long as there is no repeating input (i.e. a,a,b,c,d,d). In the output we don't see aa or dd and only one ad. Not sure if @richard_gardner expects to have any values that might repeat in the same id grouping.

[LiuZhang]

@chukleswk Yeah, this workflow is a quick answer to the table above rather than for large scale. For other programme language, I guess they must have some combinatorial algorithm to pick things out and a more efficient way of store table/values, append is not great for large tables 😅

Currently, I've used contain function which I am a bit unsure of, although I applied sort at first, I do worry there maybe special case contain will filter incorrectly. For both repeat and ordering it depends on what @richard_gardner needed. Need to change combination to permutation instead.

[chukleswk]

@LiuZhang, this is some great work! I did take what you had and modified the macro so that if it did contain duplicates in the grouping that it would find all of the matches. It would also be helpful to note that you will have to edit the append to allow more than 16 appends.

By adding a record id and then concatenating it together with something that can be pulled out with a Regex at the end, thus giving it a unique identifier.