06-25-2024 10:53 AM
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
06-26-2024 01:38 AM
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"})
}))