...

3.18 Merge-concatenate same-order sequences in the original order

 
spl-cookbook(224)

Merge records of two same-structure tables in the original order by one or more fields.
Math scores and English scores are stored separately in two files. Now we are trying to calculate the total score for each student.

Math:

CLASS STUDENTID SUBJECT SCORE
1 1 Math 77
1 2 Math 80
… … … …

English:

CLASS STUDENTID SUBJECT SCORE
1 1 English 84
1 2 English 81
… … … …

SPL script:

A
1 =T(“Math.xlsx”)
2 =T(“English.xlsx”)
3 =A1.sort(CLASS,STUDENTID)
4 =A2.sort(CLASS,STUDENTID)
5 =[A3,A4].merge(CLASS,STUDENTID)
6 =A5.groups@o(CLASS,STUDENTID; ~.sum(SCORE):TOTALSCORE)

A1 Retrieve the Math table.
A2 Retrieve the English table.
A3 Sort Math table by CLASS and STUDENTID.
A4 Sort English table by CLASS and STUDENTID.
A5 merge function merges A3 and A4 by CLASS and STUDENTID.
A6 groups function work with @o option to group A5 by CLASS and STUDENTID, during which a new group is created whenever the next neighboring value is different, and calculate the total score of each student.

Execution result:

CLASS STUDENTID TOTALSCORE
1 1 161
1 2 161
1 3 159
… … …
spl-cookbook(224)
 
 •  169 View  •