Forum Discussion

rajesh_mangipud's avatar
rajesh_mangipud
New Contributor II
2 years ago

Get order of records within a group

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

  • endor_force's avatar
    endor_force
    New Contributor III
    2 years ago

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