3. Pivot

Based on data in EmpBirthday.xlsx obtained in the previous task, (1) pivot them back to a rowwise report ordered by month and date; and (2) find the total number of employees born in February, May and August and list the counts in order.

Expected result sets:

SPL solution:

Use f.xlsimport(s) to read data from an Excel file. To import the first row as column headers of the result table sequence, use @t option in the function. When there is only one sheet in the Excel file, it is allowed to omit specifying the sheet name. We can also rewrite A1’s code as =T(“EmpBirthday.xlsx”;“Daily Statistics”), which automatically identify file format according to the extension. Here’s the retrieved crosstab:

A2 uses pivot@r() function to perform the reverse pivot. It keeps Day field, pivots the other column names to values under Month field, and transform values in the original columns to values under Count field. Execute A2 and we have the following result set:

The result set so far is still far from the expected one. The order of fields are different, and they need to be re-generated; records are ordered first by date and then by month, which is not what we want. It is important to note that Month field must not simply ordered by value, otherwise the result will be one arranged in the alphabetical order, like Apr, Aug,Dec…. To get the expected order, we need to define a sequence of month abbreviations from Januray to December in A3. Here is A3’s result set:

A4 uses A.align@a() function to group records in A2’s result set according to A3’s sequence. Here’s the result set:

A5 uses A.conj() function to concatenate records of all all groups in order. Here’s the result set:

A6 uses A.new() function to generate the expected table sequence where the fields are arranged in the required order.

In addition, we can also type =A2.sort(A3.pos(Month)) in A4 to adjust the order of A2’s records and get the result set same as A5’s. This method first gets each month’s sequence number and then sorts A2’s records according to the sequence numbers.

A8 first finds the number of employees in each month based on A6’s result set, and then selects records of the specified three months and performs alignment sorting by A7’s sequence using A.align() function.

5. Inter-table join query
Contents and Exercise Data