6.9 Alignment grouping by ordinal number: overlapping groups
Sometimes we find that multiple ordinal numbers of groups correspond to one record and need to put the record repeatedly to multiple groups according to the array of ordinal numbers.
Group posts according to tags based on the post records table and count the appearances of each tag. Below is part of the table:
| ID | Title | Author | Label |
|---|---|---|---|
| 1 | Easy analysis of Excel | 2 | Excel,ETL,Import,Export |
| 2 | Early commute: Easy to pivot excel | 3 | Excel,Pivot,Python |
| 3 | Initial experience of SPL | 1 | Basics,Introduction |
| 4 | Talking about set and reference | 4 | Set,Reference,Dispersed,SQL |
| 5 | Early commute: Better weapon than Python | 4 | Python,Contrast,Install |
| … | … | … | … |
In SPL, A.align(n.y) function uses @r option to put one record into multiple groups according to corresponding ordinal numbers during an alignment grouping operation.
SPL script:
| A | |
|---|---|
| 1 | =T(“PostRecord.txt”) |
| 2 | =A1.conj(Label.split(“,”)).id() |
| 3 | =A1.align@ar(A2.len(),A2.pos(Label.split(“,”))) |
| 4 | =A3.new(A2(#):Label,~.count():Count).sort@z(Count) |
A1 Query the PostRecord table.
A2 Split tags by comma and concatenate them into a sequence to get all distinct tags.
A3 Use A.align@r(n,y) function to group posts according to the ordinal numbers of each tag; @ option enables putting a post to multiple corresponding groups.
A4 Count the appearances of each tag and sort the numbers in descending order.
Execution result:
| Label | Count |
|---|---|
| SPL | 7 |
| Excel | 6 |
| Basics | 5 |
| … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
