Optimized and Dynamic Data Grouping

[ahmedalra]

Hi,

I have data of people, and each one have done some actions. In case there are two people that have done the same or similar actions, list these actions and group them into category 1. Then, check what actions are left, and see if there are two people that have done similar actions, and list these actions and group them into category 2. Do the same thing again and again to the actions that are left and group them into category 3, 4, 5, ...

I have done a sample workflow (attached), but I have few concerns:

1-The workflow is not dynamic. It will do the job only if there are three groups or less. In the actual data there might be thousands of groups.

2-The workflow is not optimized. It joins the inputs with itself. In the case of the sample, it is fine, it won't take too long, but the actual data have 25M records, so the performance will be very slow.

Thanks in advance,

[CharlieS]

I took your workflow and built it into an Iterative macro to solve problem #1. It will continue to loop and assign the next sequential category number. I'm not sure how this will scale, so give it a shot and let's see if this works first. 

[DavidP · Accepted solution]

I tried something different.If you use the summarize tool to group on action and concatenate person, the result looks like this:

If you then use a Tile tool configured with Unique Value for Concat_person, the result looks like this. [Tile Number] is not too far off from your result, except where there is more than 2 persons involved in an action.

Can you therefore just truncate person3 and 5 from action a and person5 from actions e and f?

You can do this with a formula tool, and then the result looks like this:

Workflow attached. Hope it's helpful.

[ahmedalra]

@DavidP

Thanks a lot. This is exactly what I was looking for since it does not deal with any Cartesian join. 

[ahmedalra]

@CharlieS

Thanks a lot for taking the time to build the macro. That was very helpful.