We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

alasweileh
6 - Meteoroid

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
UserA1
UserA2
UserA3
UserA4
UserA5
UserB1
UserB5
UserC3
UserC4
UserC5
UserD18

 

 

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
10 REPLIES 10
aatalai
15 - Aurora

@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
6 - Meteoroid

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

Carlithian
11 - Bolide
11 - Bolide

@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 😅)

 

 

 


Happy Alteryxing
alasweileh
6 - Meteoroid

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
15 - Aurora

@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

 

Screenshot 2025-07-16 183341.png

Gaurav_Dhama_
12 - Quasar

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
6 - Meteoroid

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

User Item
UserA1
UserA2
UserA3
UserA4
UserA5

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





alasweileh_0-1752695912234.png

 

Gaurav_Dhama_
12 - Quasar

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

 

Gaurav_Dhama__0-1752736780152.png

 

Gaurav_Dhama_
12 - Quasar

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

Labels
Top Solution Authors