6 Replies Latest reply: Mar 7, 2017 12:11 PM by Lauren Mills RSS

    If Match Statement

    Lauren Mills

      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!

        • Re: If Match Statement
          Lauren Mills

          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],

          • Re: If Match Statement
            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

              • Re: If Match Statement
                Lauren Mills

                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.

                  • Re: If Match Statement
                    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 ?

                  • Re: If Match Statement
                    Lauren Mills

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