How can I compare row to row?

[Coolavin]

Hello,

I am trying to figure out how to compare the rows below. It Seems like the if statements are only able to compare against one row at a time.

There are 3 columns in the example image below, "candidate id" , "exam code", "exam grade" 

I need a way to take the rows with the same "candidate id"  and compare them against each other. For example: If a candidate has an "exam grade" of "pass" for both exam 500-101 and exam 500-201 then that candidate should not show up in the table.  

 Is there a way to accomplish this?

[jwjackso · Accepted solution]

The P() function might work, below I'm using an intersection of 2 P() functions to test if the candidate has passed both exams.

=Count({<Candidate-=P({<ExamCode={'500-201'},ExamGrade={'Pass'}>}Candidate)*P({<ExamCode={'500-101'},ExamGrade={'Pass'}>}Candidate)>}Candidate)

In the first table I use the formula above to count candidates that have not passed both tests and exclude zero values.  Candidate 116114 is suppressed.  In the second table, I just count the candidates and candidate 116114 is displayed.

[Coolavin]

Hi, when I try to use what you gave me below. I get an Invalid Dimension error. 

=Count({<CandidateID-=P({<ExamCode={'500-201'},ExamGrade={'Pass'}>}CandidateID)*P({<ExamCode={'500-101'},ExamGrade={'Pass'}>}CandidateID)>}CandidateID)

Is there something I am doing wrong? or a way to tell what's causing that error?

[sunny_talwar]

From your example below, would you want to see 99878 or not? They seem to have taken only 500-201 and one of the three exam is pass, but rest is fail?

[Coolavin]

Yes, I would want to see 99878 still. 116114 should not be there though. 

[sunny_talwar]

May be this

=Count({<Candidate = {"=Count({<ExamGrade={'Fail'}>} Candidate) > 0"}>} Candidate)

[Coolavin]

I don't know what I am doing wrong. When I try to use the statement below  I get an Invalid Dimension error. 

=Count({<CandidateID = {"=Count({<ExamGrade={'Fail'}>} CandidateID) > 0"}>} CandidateID)

Is there something I am doing wrong? or a way to tell what's causing that error?

[sunny_talwar]

This was meant to be an expression, but if you need this as an dimension... replace CandidateID with this

Aggr(
    If(Count({<ExamGrade={'Fail'}>} CandidateID) > 0, CandidateID)
, CandidateID)