Get order of records within a group

[rajesh_mangipud]

I have a situation something like this:

Employee ID	Effective Date	Office
1	1/1/2023	San Francisco
1	1/1/2024	Oakland
2	6/1/2023	Sydney
2	12/1/2023	Tokyo

I need to add an order column and end date of the previous record column. Can you please suggest any ways of doing this? The output should probably look like this:

Employee ID	Effective Date	Office	End Date	Order
1	1/1/2023	San Francisco	12/31/2023	1
1	1/1/2024	Oakland		2
2	6/1/2023	Sydney	11/30/2023	1
2	12/1/2023	Tokyo		2

Best,

Rajesh

[endor_force]

Hi Rajesh,
You can probably achieve this in multiple ways. 
I try to keep it rather clean, using standard snaps, and not nest too much js code in the mappers.

My sample is first sorting based on employee id and effective date, then it is grouping all records for an employee id in to one.
Then you can generate the order and the end date individually using javascript map functions and split the employee out again.

Order = Index +1

$employee.map((item, index) => ({...item, Order: index + 1}))

 Valid to = If there is another record, fetch its effective date-1, else null.

$employee.map((item, index) => ({...item, 
ValidTo: 
index+1  == $employee.length ? null : 
Date.parse($employee[index + 1].EffectiveDate)
.minusDays(1)
.toLocaleDateString({"format": "M/d/yyyy"})
}))