RaqForum 25 No.
277 View  • 
     ...

7.25 Cross Apply operation

 
spl-cookbook(224)

Traverse multiple data files to perform Cross Apply operation on a table sequence and a sequence of values, and generate a new table sequence.
One task is to traverse all online learning terminals survey tables about a certain primary school stored in a folder, and calculate the proportion of each type of terminal.

imagepng

ID STUDENT_NAME TERMINAL
1 Rebecca Moore Phone
2 Ashley Wilson Phone,PC,Pad
3 Rachel Johnson Phone,PC,Pad
4 Emily Smith Phone,Pad
5 Ashley Smith Phone,PC
6 Matthew Johnson Phone
7 Alexis Smith Phone,PC
8 Megan Wilson Phone,PC,Pad
… … …

SPL offers A.news() function to perform Cross Apply operation on the table sequence and the sequence of terminals generated through splitting.

SPL script:

A B
1 =directory@ps(“D:/Primary School”)
2 for A1 =T(A2)
3 =@+=B2.len()
4 =B2.news(B2.TERMINAL.split@c(); ID, STUDENT_NAME, ~:TERMINAL)
5 =B4.groups(TERMINAL; count(~):Count)|@
6 =B5.groups(TERMINAL;string(sum(Count)/B3, “#.##%”):PERCENTAGE)

A1 Recursively traverse the target directory and list all files.
B2 Import Excel format survey files of all classes of all grades by loop.
B3 Calculate the total number of rows in these files, which is the total number of students.
B4 Use news() function to perform Cross Apply operation on the survey table and the sequence of split terminals.
B5 Group B4’s records by terminal, count terminals in each group and concatenate each result to the current cell. Concatenating the final result of B4 to the cell at once may result in insufficient memory.
A6 Group B5’s records by terminal again and calculate each terminal’s proportion.

Execution result:

TERMINAL PERCENTAGE
PC 70%
Pad 56.67%
Phone 93.33%
spl-cookbook(224)
 
 •  277 View  •