Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

If Statement Logic as Flag.

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

   

F2Candidate 1Candidate 2Candidate 3Candidate 4Candidate 5Candidate 6Candidate 7Candidate 8
Criteria1YESYESYESYESNONONONO

  


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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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;

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

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

vishsaggi
Champion III
Champion III

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
Creator III
Creator III
Author

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!

tresesco
MVP
MVP

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
Creator III
Creator III
Author

Thanks! That works!