Expression for marking higher seniority emps with lower rate

[#1]

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:

Project	Emp	Seniority	Country	Rate	Same/Lower Seniority with Higher Rate in Location? (Project wise)
A	E2	11	USA	15,000	0
A	E1	10	India	5,000	1
A	E8	8	India	7,000	0
A	E3	7	India	5,400	0
A	E4	7	USA	13,500	0
A	E5	6	India	4,800	0
A	E6	6	India	4,000	1
A	E7	4	India	1,500	0
B	E9	8	India	3,000	1
B	E10	8	India	4,500	0
B	E11	7	India	4,000	0
B	E12	7	India	3,800	1
B	E13	6	India	4,000	0

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

[sunny_talwar]

You need this in the script or front end?

[#3]

Front end preferably.

[sunny_talwar]

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!

[#5]

I'm on QV12.1, lets give it a shot.

[sunny_talwar]

Why is E6 marked with a 1 here?

[#7]

Because E5 has same seniority and location, but Higher Rate.

[sunny_talwar · Accepted solution]

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

[#9]

Thank you Sunny!