Checking if a user has all items in a category (subset match across two lists)

[alasweileh]

I'm trying to build a workflow that determines whether a user is a member of a category, based on whether they have all the items required for that category.

Each category has a list of required items, and each user has a list of items they own. A user is considered a member of a category only if they have every item that belongs to that category.

The goal:

I want to output which users are members of which categories. A user is a "member" of a category only if they have all items associated with that category.

Example:

Category-Item Mapping (Each Category–Item combination is unique, but the same item can belong to multiple categories) 

Category	items
A	1
A	2
A	3
B	3
B	4
B	5

User-Item Data (Each User–Item combination is unique, no duplicates)

User	Item
UserA	1
UserA	2
UserA	3
UserA	4
UserA	5
UserB	1
UserB	5
UserC	3
UserC	4
UserC	5
UserD	18

Expected Output:

User	MemberOf Category
UserA	A
UserA	B
UserB	N/A (or empty... don't matter)
UserC	B
UserD	N/A (or empty... don't matter)

I need help designing a workflow in Alteryx that can:

1. Compare user items against category item sets
2. Check if the user has all items from a category
3. Return which users are members of which categories

[aatalai]

@alasweileh slightly confusing as you go from User A, User B to User 1, is this correct? And if so where did they come from especially User 2

[alasweileh]

Sorry
I missed up the output table... just updated the post

[Carlithian]

@alasweileh 

From the data stream with the category information, I would create a column which count the number of items in that category

For the user data side I would make sure that there are no duplicates, e.g. if user A has items one twice, I only want one record for it 

Then id join the data on Item number

Finally summerize it grouping on user id and counting the distinct items that joined, compare this number to the distinct number you have for each category, if they match they are al there, if it doesn't some are missing 

(Hope this helps, answering from my phone so haven't pulled together an example 😅)

[alasweileh]

if UserA has also item 4 and 5 then it will not work, right?

UserA have 1,2,3,4,5
UserA is member of category A (from item 1,2,3) and category B (from item 3,4,5)

so the count will not be accurate since UserA count would be 5 then it will not match with the any category count

[aatalai]

@alasweileh made them into  a ocncat field and made a dynamic formula using a crew macro and did it that way, please take a look and let me know if this does the trick

[Gaurav_Dhama_]

1. Join user table with the category table on items, then you will get categories in the join output.

now add a summarise, group by user, and category, and count the distinct items.

2. take a summarize of the categories, group by category and count item,

now join 1 and 2 on the counts, with 1 on left and 2 on right.

join output will give you items that belongs to a category completely. Left anchor will give you what is not.

replying from phone, can help with a workflow tomorrow if its still not solved.

[alasweileh]

Not quite there yet... the output macro doesn't count for 

User	Item
UserA	1
UserA	2
UserA	3
UserA	4
UserA	5

UserA is member of category A (from item 1,2,3) and category B (from item 3,4,5)

[Gaurav_Dhama_ · Accepted solution]

Find the attached below, it gives the same result as you expected, just using your native tools.

[Gaurav_Dhama_]

And if you want exactly these result as posted in the question just need to add another join and union. Find the attached workflow.