If Statement Logic as Flag.

[mrthomasshelby]

Hello, I have gotten stuck on making an if statement logic in the script. I have a table as below:

F2	Candidate 1	Candidate 2	Candidate 3	Candidate 4	Candidate 5	Candidate 6	Candidate 7	Candidate 8
Criteria1	YES	YES	YES	YES	NO	NO	NO	NO

What I'm trying to do is create a flag with an if statement with the condition that if in Criteria 1, there are <5 'YES' responses, it's a 'Fail' flag and if there are >5 'YES' type responses, it's a 'Pass' flag. So, the above example will have a 'Fail' flag. I have been trying to figure out the logic for quite sometime now but couldn't arrive at a working logic. Please help me figure out the logic for this condition. TIA!

stalwar1‌

[vishsaggi]

Are your candidate 1, candidate 2 are your field names, I mean is this how your actual data looks like?

[vishsaggi]

I am not sure if this is a good way. May be there is a shorter way of doing this. Check and try below:

Tab1:
CrossTable(Criteria, Values,1)
LOAD * INLINE [
F2, Candidate1, Candidate2, Candidate3, Candidate4, Candidate5, Candidate6, Candidate7, Candidate8
Criteria1, YES, YES, YES, YES, YES, NO, NO, NO
];

Final:
LOAD F2, Criteria, Values, IF(Values = 'YES', 1, 0) AS ValFlag
Resident Tab1;

Drop Table Tab1;

using a pivot table

Add F2, Criteria and Values as Dimensions

Epxr: = IF(Sum(TOTAL ValFlag) >= 5, 'Passed', 'Fail')

[mrthomasshelby]

Wow. That's a nice approach you have there vnagaraju  . But the problem is I need this flag in the script and not expression. Can you perhaps help me out with how to modify the lines here? Much appreciated! Thanks!

[tresB · Accepted solution]

Try like:

Tab1:

CrossTable(Criteria, Values,1)

LOAD * INLINE [

F2, Candidate1, Candidate2, Candidate3, Candidate4, Candidate5, Candidate6, Candidate7, Candidate8

Criteria1, YES, YES, YES, YES, YES, NO, NO, NO

];

Join

Load

  F2,

  If( Count(if(Values='YES',Values))<5, 'Fail', 'Pass') as Flag

Resident Tab1 Group By F2;

[mrthomasshelby]

Thanks! That works!