3.5 Get XOR of two sets
There are set A and set B. XOR of set A and set B contains members that exist in A or in B but do not belong to them at the same time. For instance, the XOR of [1,2,3] and [1,3,5] is [2,5].
Student scores of two semesters are stored in two tables respectively. The task is to find students in class one in the first semester whose total score rank in top 10 only once. Below is part of the data in two tables:
First semester - SCORES1:
| CLASS | STUDENTID | SUBJECT | SCORE |
|---|---|---|---|
| 1 | 1 | English | 84 |
| 1 | 1 | Math | 77 |
| 1 | 1 | PE | 69 |
| 1 | 2 | English | 81 |
| 1 | 2 | Math | 80 |
| … | … | … | … |
Second semester - SCORES2:
| CLASS | STUDENTID | SUBJECT | SCORE |
|---|---|---|---|
| 1 | 1 | English | 97 |
| 1 | 1 | Math | 64 |
| 1 | 1 | PE | 97 |
| 1 | 2 | English | 56 |
| 1 | 2 | Math | 82 |
| … | … | … | … |
SPL can use character “%” to calculate XOR of sets. A%B calculates XOR of set A and set B.
SPL script:
| A | |
|---|---|
| 1 | =T(“Scores1.xlsx”) |
| 2 | =T(“Scores2.xlsx”) |
| 3 | =A1.groups(STUDENTID; sum(SCORE):SCORE) |
| 4 | =A2.groups(STUDENTID; sum(SCORE):SCORE) |
| 5 | =A3.top(-10;SCORE).(STUDENTID) |
| 6 | =A4.top(-10;SCORE).(STUDENTID) |
| 7 | =A5%A6 |
A1: Import SCORES1 from the original table.
A2: Import SCORES2 from the original table.
A3: Group A1 by STUDENTID and calculate the total score of each student.
A4: Group A2 by STUDENTID and calculate the total score of each student.
A5: Get IDs of students whose total scores rank in top 10 in the first semester.
A6: Get IDs of students whose total scores rank in top 10 in the second semester.
A7: Use character “%” to calculate XOR of A5 and A6.
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
