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

# To find rows with conditions

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

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.

Hope it helps,

Regards,

Norbert

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

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

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

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,