3.11 Belong to & Contain: check whether a set contains one or more certain objects
Our task is to find students who select both Matlab and Modern Wireless Communication System based on COURSE table and SELECT_COURSE table. Below are parts of two tables:
COURSE:
| ID | NAME | TEACHERID |
|---|---|---|
| 1 | Environmental protection and sustainable development | 5 |
| 2 | Mental health of College Students | 1 |
| 3 | Matlab | 8 |
| 4 | Electromechanical basic practice | 7 |
| 5 | Introduction to modern life science | 3 |
| 6 | Modern wireless communication system | 14 |
| … | … | … |
SELECT_COURSE:
| ID | STUDENT_NAME | COURSE |
|---|---|---|
| 1 | Rebecca Moore | 2,7 |
| 2 | Ashley Wilson | 1,8 |
| 3 | Rachel Johnson | 2,7,10 |
| 4 | Emily Smith | 1,10 |
| 5 | Ashley Smith | 5,6 |
| … | … | … |
The task is not complex. We need to find whether each COURSE field value in SELECT_COURSE table contains the set of courses [3,6](which are IDs of two target courses).
SPL A.pos(B) function gets position of a member of set B in set A and returns null if such a member does not exist in set A.
SPL script:
| A | |
|---|---|
| 1 | =T(“Course.xlsx”) |
| 2 | =T(“SelectCourse.xlsx”) |
| 3 | =A1.select(NAME==“Matlab” || NAME==“Modern wireless communication system”).(ID) |
| 4 | =A2.run(COURSE=COURSE.split@cp()) |
| 5 | =A4.select(COURSE.pos(A3)!=null) |
A1: Import Course table from the original file.
A2: Import SelectCourse table from the original file.
A3: Get the set of IDs of the two target courses.
A4: Split each Course value in SelectCourse table with comma and parse the set of numbers.
A5: A.pos() function locates IDs of the two target courses in each Course value set. A record is eligible if the function does not return a null.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
