If Match Statement

[#1]

Hello,

I need help to make my IF (MATCH) statement more specific to my needs.

My current statement ...

If(Match(transcript_status_code, 'A', 'X') and category = 'Skill Area 01/08',

employee_first_name&' '&employee_last_name) as [Skill Area 1 Complete]

Each 'Category' has 'Levels' and each 'Level' has 'Courses'.

Right now, this is giving me the employee who has an A or a X in any Course within the Category.

Lets say I want to add 'Level' = Level 1 and any Course that falls into that Level.

But I only want the name of the employee who has an A or a X in ALL of the Courses within Level 1. Not the employee who has at least 1 A or a X in a course within Level 1.

Any help helps!

[#2]

I thought maybe like this ... but it doesn't like it.

If(Match(transcript_status_code, 'A', 'X') and category = 'Skill Area 01/08' and course_name = p({<level = {'Level 1'}>}),

employee_first_name&' '&employee_last_name) as [Skill Area 1 Complete],

[shubham_singh]

I am unable to understand your problem. However I'll still try to guess.

Try this

If(Match(transcript_status_code, 'A', 'X') and category = 'Skill Area 01/08' and Level='Level 1',1,0) as [EmployeeScore]

then take the resident of this table use this

if(Sum(EmployeeScore)=count(Course),employee_first_name&' '&employee_last_name) as [Skill Area 1 Complete]

*use proper group by statement otherwise it won't work.

If an emplyee has 5 courses and he gets an A or X in all of them that shoud generate a total score of 5 that is equal to number of courses.

Hope it Helps

-Shubham Singh

[shubham_singh]

You want to do this on UI or script ?

[#5]

There is no physical score - I just want the names of the employees who has a A or X in every course for the Level = 1 for the category. I don't want to assign values or do a sum.

This is being done in script.

[shubham_singh]

Since this is being done in script

course_name = p({<level = {'Level 1'}>})

this is an incorrect syntax of set analysis that is used in UI not script.

It doesn't matter if there are physical score or not. I just assigned value to a score field. If an employee has an A or X in every course then his/her score will be 1 for each course. Since there are multiple courses the sum of the score for an employee for Level=1 for the category will be equal to number of courses for that employee i.e. count.

Get it ?

[#7]

Okay - I get what you are saying now lol . Let me try this!