5 Replies Latest reply on Dec 30, 2018 1:25 AM by Antonio Salas Mena

    To find rows with conditions

    Antonio Salas Mena

      Hello Everyone,

       

      We have a table with several records with the following columns:

       

       

      ID
      Group
      LevelLevel Descriptive
      Annual Salary
      1Systems6CEO190.000
      2Systems5Directive140.000
      3Systems4Developer80.000
      4Adminstration5Directive130.000
      5Adminstration2Worker60.000
      6etc...

       

      The question is: I need to identify all the employees who are earning more than their superior. Create a formula to verify the record to record if there is a superior in your area that is earning less than he, so I do not know how to make this comparison.

       

      Thk

       

      Antonio

        • 1. Re: To find rows with conditions
          Norbert Maijoor

          Hi Antonio,

           

          Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread

           

           

          1. D1. Level 2: {fixed [Group]:max(if [Level]=2 then [Annual Salary] end)}

           

          2. D2. Level 4: {fixed [Group]:max(if [Level]=4 then [Annual Salary] end)}

           

          3. D3. Level 5: {fixed[Group]:max(if [Level]=5 then [Annual Salary] end)}

           

          4 D4. Level 6: {fixed [Group]:max(if [Level]=6 then [Annual Salary] end)}

           

          5. D5. Earnings:

          if [Level]=6 then "Not applicable"

          elseif [Level]=5 and [Annual Salary]>[D4. Level 6] then  "Earning more"

          elseif [Level]=4 and ([Annual Salary]>[D3. Level 5] or [Annual Salary]>[D4. Level 6]) then "Earning more"

          elseif [Level]=2 and ([Annual Salary]>[D2. Level 4] or [Annual Salary]>[D3. Level 5]) then "Earning more"

          else "Earning less"

          END

           

          6. Drag the required objects to the indicated locations.

           

          dragit.png

           

          Hope it helps,

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: To find rows with conditions
            Antonio Salas Mena

            Hi Robert,

             

            Thanks for your help, but I need to find if any employee earn more than any his superiors and not if earn more than the maxim salary of all superiors.

             

            I think t is necessary to make an array from all salaries of his superiors and then to compare if his salary is higher than any record in this array.

             

            This is possible? make an array and after this compare?

             

            thanks again

             

            Antonio

            • 3. Re: To find rows with conditions
              Antonio Salas Mena

              Hi Robert,

               

              I think I can use your solution, but I need change MAX for MIN:

               

              {fixed [Group]:max(if [Level]=2 then [Annual Salary] end)}

               

              {fixed [Group]:min(if [Level]=2 then [Annual Salary] end)}

               

              If then employee earn more than min salary of his superiors I can select it.

               

              Thanks a lot.

               

              Antonio

              1 of 1 people found this helpful
              • 4. Re: To find rows with conditions
                Norbert Maijoor

                Hi Antionio,

                 

                Thanks for the feedback. Please close the thread with a "Helpfull" or "Correct Answer" so the community is aware and can focus on other challenges,

                Your cooperation is appreciated:)

                 

                Regards,

                Norbert

                1 of 1 people found this helpful