Overlap analysis

[gg96]

Hello, 

I am reaching out to this community for the first time for help with an analysis. Looking forward to the collaboration! I have a set of data with two columns - user id (00001, 00002, 00003) and group designation (A, B, C, D). Each username can be assigned to multiple different groups. I am trying to create a matrix that indicates the % of overlap of usernames between groups (in other words, how many usernames of group A are also in group B, C, and D?) No geography/spatial relevance is involved in this analysis. 

I have included two comparable requests from other forums below that better demonstrate the work I am trying to do and also depict sample outputs here -

1) https://stackoverflow.com/questions/30873307/calculating-overlap-between-groups

2) https://stackoverflow.com/questions/32188560/category-overlap-analysis

Solutions were provided in these other forums for SQL and R. I was wondering if the same could be done in Alteryx, before I tried downloading softwares/data tools I have no experience with. 

Any help would be appreciated. Happy to provide further color, information if needed!! 

Thanks. 

[BenMoss · Accepted solution]

Interesting challenge and I'm sure there are a few ways of reaching the same answer with this one, but here is my attempt. I have attached the workflow to this post (Y).

Ben

[jdunkerley79]

Here's my go:

1. Self join the record to get all the crosses

2. Work out the total count for each pair

3. Cross tab this to make the table

4. Work out total distinct items

5. Create percentages

Sample attached

[gg96]

This worked beautifully!

Simple solution as well! I was thinking forward towards batch/iterative macros but I underestimated the value of Join and Summarize. 

Thank you so much :)