3.23 Merge-concatenate multiple table sequences by primary key in order
Merge records of table sequences of same structure in order.
Find students who have had a fever for at least 3 days based on body temperature records from June 1st to 20th.
| StudentID | Name | Fever |
|---|---|---|
| 10 | Ryan | 0 |
| 5 | Ashley | 0 |
| 13 | Daniel | 1 |
| 19 | Samantha | 0 |
| 1 | Rebecca | 0 |
| … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =to(601, 620) |
| 2 | =A1.(T(string(~)+“.xlsx”)) |
| 3 | =A2.(~.keys(StudentID).sort(StudentID)) |
| 4 | =A3.merge() |
| 5 | =A4.group@o(StudentID,Fever) |
| 6 | =A5.select(~.Fever==1 && ~.len()>=3).id(Name) |
A1 Create a sequence of table names.
A2 Retrieve data from files from June 1st to 20th circularly.
A3 Set StudentID as primary key and sort all data by the key.
A4 Use merge function to merge data in order by comparing primary key values.
A5 group function works with @o option to group A4’s records by StudentID, during which a new group is created when grouping field value changes.
A6 Get names of students who have had a fever for at least 3 days.
Execution result:
| Name |
|---|
| Ashley |
| Rachel |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
