Generate Numbers for the Same Group of Data

 

Example

The data of the Excel file book1.xlsx is shown in the figure below:

..

The task is to renumber the UNIQUE ID. If IDs are the same, the numbers will be the same, and the number of different IDs will be different. The result is shown in the following figure:

..

Write SPL script:


A

1

=T("e:/work/book1.xlsx")

2

=A1.group(ID).run(a=#,~.run('UNIQUE ID'=a))

3

=T("e:/work/book2.xlsx",A1)

A1 Read book1.xlsx data

A2 After grouping by ID, loop each group, assign each group's serial number # to variable a, and loop each record in the group, making UNIQUE ID'=a

A3 Save the table sequence in A1 to the file book2.xlsx