Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I feel like I am tunel visioned today and I am unable to find a solution for below simple problem:
I need to write an expression which will flag employees as below:
Rules:
- Within a Project and Location we are finding out if Employee with Same/Lower seniority exists and has higher Rate
- If yes - marking line with 1, else with 0.
Example:
- E1 is marked with 1 as employee with lower seniority (E3) exists in Project A and location India.
BR,
Kuba
Script
Table:
LOAD *,
Seniority - (Rate/1E10) as Sort;
LOAD * INLINE [
Project, Emp, Seniority, Country, Rate
A, E2, 11, USA, "15,000"
A, E1, 10, India, "5,000"
A, E8, 8, India, "7,000"
A, E3, 7, India, "5,400"
A, E4, 7, USA, "13,500"
A, E5, 6, India, "4,800"
A, E6, 6, India, "4,000"
A, E7, 4, India, "1,500"
B, E9, 8, India, "3,000"
B, E10, 8, India, "4,500"
B, E11, 7, India, "4,000"
B, E12, 7, India, "3,800"
B, E13, 6, India, "4,000"
];
Expression
=Aggr(If(Rate < RangeMax(Above(Rate, 0, RowNo())), 1, 0), Project, Country, (Sort, (Numeric)))
You need this in the script or front end?
Front end preferably.
This can be possible on the front end if you have correctly sorted data where data is sorted by Project and then by Location and finally by Seniorty ascending or you have QV12 or above so that we can use The sortable Aggr function is finally here!
I'm on QV12.1, lets give it a shot.
Why is E6 marked with a 1 here?
Because E5 has same seniority and location, but Higher Rate.
Script
Table:
LOAD *,
Seniority - (Rate/1E10) as Sort;
LOAD * INLINE [
Project, Emp, Seniority, Country, Rate
A, E2, 11, USA, "15,000"
A, E1, 10, India, "5,000"
A, E8, 8, India, "7,000"
A, E3, 7, India, "5,400"
A, E4, 7, USA, "13,500"
A, E5, 6, India, "4,800"
A, E6, 6, India, "4,000"
A, E7, 4, India, "1,500"
B, E9, 8, India, "3,000"
B, E10, 8, India, "4,500"
B, E11, 7, India, "4,000"
B, E12, 7, India, "3,800"
B, E13, 6, India, "4,000"
];
Expression
=Aggr(If(Rate < RangeMax(Above(Rate, 0, RowNo())), 1, 0), Project, Country, (Sort, (Numeric)))
Thank you Sunny!