Counting rows with same values in other columns

[bbaisil]

Hi,

I have a question. My data looks like something below

Id    Grade

1       A

1       A

1       B

1       C

2       C

2       D

2       A

2       U

3       U

3       U

3       U

3       U

4        A

4       B

4       U

4       A

5       U

5       U

5       U

5       U

6       A

6       A

7       A

7       U

7       B

8       A

8       A

8       A

These are the grades achieved for different units by  students(id). I have around 90K records. I want to know the students who got U in all units. Can anyone help ?

[IraWatt]

Hey @bbaisil,

I assume as there isnt a units column you are looking for people with 4 U grades. Here is one way to do this:

In the summarize tool I grouped by student ID and Grade then counted. 

In the filter I used a formula the community has some quick and easy videos on formulas https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20... 

Any questions or issues please ask :)
HTH!
Ira

[Amol_Telore]

Hey @bbaisil 

Another way of doing this, similar to @irawatt solution.

[bbaisil]

Hi, Number of units are not always 4. there are some students who only have 3 or 2 units enrolled in my data sets.

Units column is also available if that helps.

[IraWatt · Accepted solution]

@bbaisil, I updated my workflow:

I hope the annotations make sense. I count how many grades each student has to find out how many units the enrolled on.

[Kinga · Accepted solution]

Hi @bbaisil ,

I've added student 6 (with three U only) and student 7 (with one A and two U), just to check, if it is working.

I've add too summarize tools: one to check how many units has each student (grouping only by id) and second grouping by id  and grade. If count of "U" for this student in second tool equals to count of all student units, then this student has only "U" grades. 

[bbaisil]

Hi, Thank you both.

It worked now