Identify records with multiple categories within a group

[keeprollin]

Hello community,

In the given sample dataset, each part belongs to a certain part category, for example, Part A, Part B, Part C and Part D belong to 'abc' category while Part P, Part Q, and Part R belong to 'xyz' category.

Case No	Part No	Part Category
1	Part A	abc
1	Part P	xyz
2	Part C	abc
3	Part C	abc
3	Part Q	xyz
4	Part D	abc
4	Part D	abc
5	Part R	xyz
5	Part R	xyz
6	Part B	abc
6	Part A	abc
6	Part P	xyz

I'm looking to find the cases that use a combination of categories abc and xyz for each Case No. I'm not interested in getting those cases which have only one Category attributed to it. For example, Case No 1 has both categories abc and xyz, and Case No 2 has only only category abc attributed to it, so I would not include it in my output. Similarly, Case No 3 and 6 have both categories attributed to it while Case No 4 and 5 do not. So, I would include only Case No 3 and 6. 

My final Table would look something like the one below:

Case No	Part No	Part Category
1	Part A	abc
1	Part P	xyz
3	Part C	abc
3	Part Q	xyz
6	Part B	abc
6	Part A	abc
6	Part P	xyz

[Luke_C]

Hi @keeprollin 

Are you specifically looking for the two categories? Or looking for any cases that simply use more than 1 category? 

[DataNath · Accepted solution]

How's this?

[IraWatt]

Hey @keeprollin,

This approach groups by Case No and counts the distinct part categories. The Filter then only passes Case No's with more then two part categories. The center join then filters off records which don't match.   

To change to matching to just two distinct values you can just change the filter to only allow distinct_catagories = 2

HTH,

Ira