Group Rows, Concatenate Values of a Specified Column in each Group and Add Them to a New Column
Problem description
The following Excel file has two columns grouped by Name and marked by ID number respectively, as shown below:
The task is to combine the ID numbers of the same name in a specified format and put the combined values in column C. Below is the expected result:
Directions
1. Select data area of A1:B13 in the Excel file and press Ctrl+C to copy data.
2. Select cell A1 in esProc to paste the data in.
3. Write the following code to implement the task:
A |
B |
|
1 |
……(Data pasted from Excel) |
|
2 |
=A1.import@t() |
|
3 |
=A2.group@o(Name) |
|
4 |
=A3.conj([~]* ~.len()) |
|
5 |
=A4.(~.(ID)) |
|
6 |
=A5.concat@n(“|”) |
A2 Import A1’s text data as a table sequence; @t option enables to read the first row as field names.
A3 Group rows by Name field. Since the Name values are already grouped, @o option enables to use the existing groups directly.
A4 Copy each group of rows according to the length of the group.
A5 Get ID values from each group of table sequence.
A6 Concatenate ID values in each group as a string with the vertical line “|” and join strings up as a large string where line break is used to separate rows.
4. After the code is executed, copy A6’s result and paste it into C2 in the Excel file.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
