cancel
Showing results for 
Search instead for 
Did you mean: 

Get order of records within a group

rajesh_mangipud
New Contributor II

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 IDEffective DateOfficeEnd DateOrder
11/1/2023San Francisco12/31/20231
11/1/2024Oakland 2
26/1/2023Sydney11/30/20231
212/1/2023Tokyo 2

 

Best,

Rajesh

1 REPLY 1

endor_force
New Contributor III

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.

endor_force_0-1719390121238.png

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