Bucketing data into grouping of common characteristics

[jeff_ard]

Hello!

I have a list of SKUs.  Each SKU is assigned to multiple sources. (The data goes down - each SKU will repeat for each source).  I need to "bucket" the SKUs into groupings that all have an identical source list.

I have accomplished this by sorting the data by Source, then using summation to concatenate the sources to make a key.  This is far from an ideal solution as there are over 100 sources for a SKU in some cases.

Is there a more elegant approach?  I am hoping this one is as simple as a tool I don't know about! I don't like thousand character concatenated strings as keys!

*I explained the simple problem above - but the actual problem involves multiple "group by" fields and this assessment must be made in each grouping.  Think of each SKU being in a category, and each category being in a region.

I will make some sample data and upload the working concatenate if this one proves to be more complex than a quick answer!

Thanks everyone!

[T_Willins]

Hi @jeff_ard,

You should be able to group by more than one field in a Summarize tool.  Order matters, so think of each group by as an expand of the group by above it.  In your case I think you would want to group by source, then group by SKU.  If you need further assistance a sample data set would be helpful.

[jeff_ard]

Thanks for the reply!

Unless I am missing a use of the summarize tool (which is quite possible), I don't think this will quite solve it.

I attached a sample flow - this flow does have the intended result - but I am very concerned with scalability of this solution.

[T_Willins · Accepted solution]

Hi @jeff_ard,

Your workflow actually works well for what you are trying to do, which is different than what I first thought.  Attached is your workflow and how I was seeing if I could make it more efficient - but I think yours works just as well. In the workflow Comment box you mention you want to rename each analysis group. If I understand what you are looking for, the last two tools do that. 

[jeff_ard]

Tiny concern with cross tabbing 10,000 SKUs across - but we will see what happens. This gave me a few new tools to play with to try other ways. Thanks for the help!

[T_Willins]

Hi @jeff_ard,

If you are running into problems with the cross-tab, you might be able to break up the data and use a batch macro to process the data in chunks.