How to search for multiple rows within a data extension and IF one of rows equals certain value THEN

Question

So a Contact has one Competence profile in the object: CM_Competence_Profile__c_Salesforce

This Competence profile could have multiple evidences (rows) within the object: CM_Evidence__c_Salesforce

These rows within CM_Evidence__c_Salesforce object all got a field named: Evidence_Type__c

Evidence_Type__c is a text field which can contain 255 characters max.

What i want is evaluate every row which matches the Id of CM_Competence_Profile__c_Salesforce within the CM_Evidence__c_Salesforce if they have the following values in field Evidence_Type__c: "Competence Self-Assessment" or "Technical Self-Assessment" or "Interview/Written Assignment". By the way this field Evidence_Type__c can have more different values then just the three mentioned above.

The catch is, i want to show specifically if one or more of those rows are missing.

So for instance if "Interview/Written Assignment" is not present but the other two are. I want to show the content: "Interview/Written Assignment is missing"

I tried building this ampscript, but it's not working for me. Can someone help me optimizing this?

%%[
SET @contactid = AttributeValue('Contact Id') /* Better handling of NULL values */

SET @competence = Lookup('CM_Competence_Profile__c_Salesforce','Id','Candidate__c',@contactid)
SET @medical = Lookup('CM_Competence_Profile__c_Salesforce','Medical_Certificate__c','Candidate__c',@contactid)
SET @safety = Lookup('CM_Competence_Profile__c_Salesforce','Safety_Certificate__c','Candidate__c',@contactid)
SET @education = Lookup('CM_Competence_Profile__c_Salesforce','Education_Degree__c','Candidate__c',@contactid)

SET @rows = LookupRows('CM_Evidence__c_Salesforce','Competence_Profile__c',@competence)
SET @RowCount = rowcount(@rows)

IF @RowCount > 0 THEN

  FOR @i = 1 TO @RowCount DO

    VAR @DEColumn
    SET @row = row(@rows,@i) /* get row based on counter */
    SET @DEColumn = field(@row,"Evidence_Type__c")

  ]%%

        %%[ IF @DEColumn == 'Competence Self-Assessment' THEN ]%%

        Competence Self-Assessment is NOT present<BR><BR>


        %%[ ELSEIF @DEColumn == 'Technical Self-Assessment' THEN ]%%

        Technical Self-Assessment is NOT present<BR><BR>


        %%[ ELSEIF @DEColumn == 'Interview/Written Assignment' THEN ]%%


        Interview/Written Assignment is NOT present<BR><BR>

        %%[ ENDIF ]%%


   %%[ NEXT @i ]%%

    %%[ ELSE ]%%

    No rows found

    %%[ ENDIF ]%%

Answer 1

So eventually i figured it out myself. I was thinking to complicated by first getting all the rows in and then check if one of those rows contain a certain value. What i now did was just a simple lookup on the CM_Evidence__c_Salesforce object with two criteria specified. So i do not have to use LookupRows at all. This is my code:

%%[
SET @contactid = AttributeValue('Contact Id') /* Better handling of NULL values */

SET @competence = Lookup('CM_Competence_Profile__c_Salesforce','Id','Candidate__c',@contactid)
SET @medical = Lookup('CM_Competence_Profile__c_Salesforce','Medical_Certificate__c','Candidate__c',@contactid)
SET @safety = Lookup('CM_Competence_Profile__c_Salesforce','Safety_Certificate__c','Candidate__c',@contactid)
SET @education = Lookup('CM_Competence_Profile__c_Salesforce','Education_Degree__c','Candidate__c',@contactid)

SET @tsa = Lookup('CM_Evidence__c_Salesforce','Evidence_type__c','Evidence_type__c','Technical Self-Assessment','Competence_Profile__c',@competence)
SET @csa = Lookup('CM_Evidence__c_Salesforce','Evidence_type__c','Evidence_type__c','Competence Self-Assessment','Competence_Profile__c',@competence)
SET @interview = Lookup('CM_Evidence__c_Salesforce','Evidence_type__c','Evidence_type__c','Interview/Written Assignment','Competence_Profile__c',@competence)
]%%

%%[IF EMPTY (@tsa) THEN]%%
Technical Self-Assessment not present<BR>
%%[ELSE]%%
%%[ENDIF]%%


%%[IF EMPTY (@csa) THEN]%%
Competence Self-Assessment not present<BR>
%%[ELSE]%%
%%[ENDIF]%%


%%[IF EMPTY (@tsa) THEN]%%
Interview/Written Assignment not present<BR>
%%[ELSE]%%
%%[ENDIF]%%


%%[IF EMPTY(@medical) THEN]%%
Medical Certificate not present<BR>
%%[ELSE]%%
%%[ENDIF]%%

%%[IF EMPTY(@safety) THEN]%% 
Safety Certificate not present<BR>
%%[ELSE]%%
%%[ENDIF]%%

%%[IF EMPTY(@education) THEN]%%
Education Degree not present<BR>
%%[ELSE]%%
%%[ENDIF]%%