Finding all possible combinations of each value

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.

Combination.yxzp

All comments

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.

All Possible Combinations.yxmd

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

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?

All Possible Combinations.yxmd

richard_gardner

Hi @chuckleswk

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.

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

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.

Combination.yxzp

@leozhang2work'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-n/

@leozhang2work 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.

@chuckleswk 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.

@leozhang2work, 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.

Combination.yxzp

Good job @chuckleswk, let's wait and see if there is more requirement about it.

In the main time, suggest it to Alteryx challenges 😁

@leozhang2work I was thinking the exact same thing after I got through my first little attempt at it! I was going to sit don and run the whole thing with 28+ entries overnight to see how long it would actually take.

@richard_gardner if this works or you, then could you please accept @leozhang2work's solution so that others may find it!

richard_gardner

Thank you @leozhang2work and @chuckleswk for all of your help with this. No permutations are needed for my use case, so I wrapped this in a batch macro and it worked perfectly. Thank you so much!

For others who come looking for a general tool for working out Combinations, I have a macro up on the Public Gallery that will "pick k items from a list of n items" as a combination, using an MIT algorithm based on the fact that binary numbers are unique combinations of 1 and 0. Find it here... https://community.alteryx.com/t5/Public-Community-Gallery/Combinations/ta-p/1023099

Deepakchandrashekar

@LiuZhang can you please help me, absolutely awesome macro. I have a problem statement on similar lines, but a small tweak

I am looking to create combinations based on groups

Attached is a sample where in I have 4 groups and associated numbers. not sure how I can use your macro to get them to give me combinations by group

@Deepakchandrashekar Is this the sort of expected result...

I have not put the combinations into columns, as this format is easier to paste, and it is trivial to change the format, but the image should show the results clearly.

Deepakchandrashekar

Yes , @Hiblet

Basically looking to cover all combinations in a given group.

THank you for helping me out

@Deepakchandrashekar Attached is an export of the flow. The flow calls a batch macro, which in turn wraps my ComboMonster macro (available on the Gallery). I basically pack each task for the ComboMonster into one record, and the batch macro pushes those packed records one at a time. Beware putting data into this where you have 10 or more items in the group, as combinations become huge very quickly.

Quick Plug: I am a contractor for Continuum Jersey, we are a consultancy who specialise in Financial and Automation solutions, and we are strongly pushing into AI and how AI can be used today by businesses. You might be interested in this ChatGPT connector... https://community.alteryx.com/t5/Community-Gallery/OpenAI-ChatGPT-4-Completions-Connector/ta-p/1116364

Demo_1_Groups.yxzp

Deepakchandrashekar

@hiblet thank you so much for this macro, I had to make some tweaks to suit my need... but it works like a charm....

on the AI solution, I will check with my company if we could access them and keep you posted..

Great, good to know that it is useful. On the AI side, the ChatGPT connector is effectively just a wrapper to make calls to the OpenAI API endpoints, so if your company allows this, then the macro would be useful. It is only a macro because this allows me to make the calls throttled, and to make repeated calls if the endpoint is busy. Good luck, Steve.

Hey @chuckleswk - thanks so much for posting this. I was able to use this to solve my task. I had a question for you as I saw that you added a box in the main workflow named 'used in future to reduce iteration run.' I have a lot of data running through this macro and it looks like it is going to take a long time to process. I was curious how I could implement what you set up in this box to speed up the processing speed of the iterations? Thank you!!

https://community.alteryx.com/t5/Community-Gallery/Combinations/ta-p/1023099

I've got a macro on the Community pages that does not use iterations. There is a nice solution to the Combinations problem that uses binary numbers. If you want three things from eight, you can use all eight-bit binary numbers with three 1's in them to find all possible combinations very quickly, and you are guaranteed to find all of them. This should be faster than looping, and also more robust, in that you cannot miss a combination due to a coding error. Here is the macro link...

@Hiblet I did look into your solution but I don't think it produces what I'm looking for as it appeared to only allow you to pick combinations of a certain number but I wanted as follows:

input: abc

output: a / b / c / a,b / a,c / b,c / a,b,c

Hi @data4accounting; OK, so, similar to the original poster. If you did want to speed things up and avoid iteration, you just use multiple instances of the macro tool, and union the results.

It looks like in your example above you want 1 from 3, 2 from 3 and 3 from 3. For this, you would just have three instances of the macro, each taking a copy of the same input stream. Each macro is configured to take 1, 2 and 3 from 3, and you union the results. Not only would this be faster than iteration, it would work well with AMP, as Alteryx would work the streams in parallel.

If you have an example data set that you can share, I would be happy to knock you up a demo.

Of course, you don't have to use the macro, but it seems that you are looking for performance, so it might be worth you trying it out to see if it helps.

Either way, I hope you find a good solution.

@Hiblet - would this solution be scalable for x from x, as well? The issue I have is that the from 3 is not consistent but the iterative macro option is taking a really long time to run. I have attached the combinations I am looking to generate where Item Mapping ID 1 is my unique grouping identifier and then Row ID is the possible values for each grouping where the output is 2^# of Row IDs - 1.

For example, the output for Item Mapping ID 1 is 2^2-1 or 3 combinations as follows: 1 / 2 / 1,2

The output for Item Mapping 2 is 2^7-1 or 127 combinations as follows:

1
2
3
4
5
6
7
1,2
1,3
1,4
1,5
1,6
1,7
2,3
2,4
2,5
2,6
2,7
3,4
3,5
3,6
3,7
4,5
4,6
4,7
5,6
5,7
6,7
1,2,3
1,2,4
1,2,5
1,2,6
1,2,7
1,3,4
1,3,5
1,3,6
1,3,7
1,4,5
1,4,6
1,4,7
1,5,6
1,5,7
1,6,7
2,3,4
2,3,5
2,3,6
2,3,7
2,4,5
2,4,6
2,4,7
2,5,6
2,5,7
2,6,7
3,4,5
3,4,6
3,4,7
3,5,6
3,5,7
3,6,7
4,5,6
4,5,7
4,6,7
5,6,7
1,2,3,4
1,2,3,5
1,2,3,6
1,2,3,7
1,2,4,5
1,2,4,6
1,2,4,7
1,2,5,6
1,2,5,7
1,2,6,7
1,3,4,5
1,3,4,6
1,3,4,7
1,3,5,6
1,3,5,7
1,3,6,7
1,4,5,6
1,4,5,7
1,4,6,7
1,5,6,7
2,3,4,5
2,3,4,6
2,3,4,7
2,3,5,6
2,3,5,7
2,3,6,7
2,4,5,6
2,4,5,7
2,4,6,7
2,5,6,7
3,4,5,6
3,4,5,7
3,4,6,7
3,5,6,7
4,5,6,7
1,2,3,4,5
1,2,3,4,6
1,2,3,4,7
1,2,3,5,6
1,2,3,5,7
1,2,3,6,7
1,2,4,5,6
1,2,4,5,7
1,2,4,6,7
1,2,5,6,7
1,3,4,5,6
1,3,4,5,7
1,3,4,6,7
1,3,5,6,7
1,4,5,6,7
2,3,4,5,6
2,3,4,5,7
2,3,4,6,7
2,3,5,6,7
2,4,5,6,7
3,4,5,6,7
1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,6,7
1,2,3,5,6,7
1,2,4,5,6,7
1,3,4,5,6,7
2,3,4,5,6,7
1,2,3,4,5,6,7

As seen, some of these get quite large so any help to optimize speed would be appreciated!

Example Combinations.xlsx

Hi @Hiblet - is this scalable to x from x combinations? The issue I have is that the from 3 is not consistent. I have attached all example combinations where Item Mapping 1 ID is the unique combination identifier and the Row ID is the number of possible values where 2^# of Row IDs -1 is the total combinations. For example, Item Mapping 1 ID = 1 should output 1 / 2 / 1,2 but then Item Mapping 1 ID = 2 has 7 different values so the output for that one is 127 different combinations and some of these get quite large.

Example Combinations.xlsx

Hi @data4accounting , the problem here is really that Combinations scale factorially. In your Spreadsheet, you have group 19 that has 888 records. The number of possible combinations for 888 items is approx 2.6 x 10e1741, ie mind-boggling huge. For perspective, it is estimated by boffins that the number of atoms in the observable universe might be around 10e82, a 1 with 82 zeros after it. 10e1741 is a 1 with 1741 noughts after it. We may have to wait for quantum computers to work this stuff out, but don't hold your breath.

Just having 12 items gives you 6.2 billion possible combinations.

Is it possible to restrict your problem domain to smaller groups?

@Hiblet - yes I realized that. I was actually able to tailor your macro and use it for all combinations because there is just a filter in the middle doing the pick selection but as your mentioned, my combinations are just too much to process so I'm trying to figure out how we can truncate the population.