...

Add Aggregation of Same Category

 

Example1

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

..

The task is to combine the ID numbers of the same name in a specified format, and then put the combined value in column C. The expected results are as follows:

..

Write SPL script:

A

1

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

2

=A1.group(Name).run(a=~.(ID).concat("|"),~.run(IDs=a))

3

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

A1 Read the data of book1.xlsx and add a column of IDs

A2 After grouping the data by Name, loop through each group, concatenate the ID value of each group into a string with | and assign it to variable a, and loop through each record in the group to make IDs=a

A3 Store the table sequence in A1 to book2.xlsx

 

Example2

The data of the Excel file book1.xlsx is shown in the following figure, where Employee column is the employee's name, Shift column is the commuting mark, and Route ID column is the number of the shuttle bus the employee takes.

..

The task is to sum up the number of employees who commute to and from work on the same bus every day. The result is shown in the green area of the figure below:

..

Write SPL script:

A

1

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

2

=A1.group('Route ID').run(a=~.count(),~.run(Employees=a))

3

=A1.group(Employee,Shift)

4

=A3.new(Employee,Shift,"{"/~.(Employees).concat(";")/"}":Clubbing)

5

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

A1 Read the data of book1.xlsx and add a column of Employees

A2 After grouping the data by Route ID, loop through each group, calculate the number of records in each group (the number of people in the same bus) and assign it to variable a, and loop through each record in the group to make Employees=a

A3 Group A1 again by Employee and Shift

A4 Use each group in A3 to create a new record respectively, retrieve the Employee and Shift fields, concatenate the Employees of each record in the group into a string with semicolons, add braces on both sides, and name it as the Clubbing field.

A5 Store the table sequence in A4 to book2.xlsx


 
 •  278 View  •