2. Test data preparation

Based on file EmpInfo.txt created in the previous section, find the number of people born on each day, return result set as a crosstab, where column names are abbreviations of English month names and the first column is the date, and export the result data to Excel file EmpBirthday.xlsx.

Expected result set:

SPL code:

A1 reads data from the table sequence containing employee information using T(fn) function, which automatically identifies the file extension and reads data as a table sequence. The code is equivalent to =file(“EmpInfo.txt”).import@t(). A2 groups records and summarizes data, where a two-layered grouping operation is specified – group records by birthday month and then by date – and count() function is used to get the number of employees in each group. Execute A2 and we have the following result:

As the month is required to be written in abbreviations, we need to perform further computations. A3 generates dates in each of the twelve months by loop, and uses string(dt, fmt) function, where the month abbreviation display format is specified as “EEE”, to get a result set as follows:

A4 loops through A2’s table sequence to modify Month field values as corresponding English abbreviations. Now A2’s result becomes this:

The result set is actually a table using two dimensions Month and Day. A5 uses A.pivot()function to pivot A2’s table sequence to one using the required dimensions. That is the expected crosstab. A6 uses f.xlsexport(A) function to export A5’s result set to an Excel file. The function works with @t option to export the first row as column headers and specifies name of the sheet to which data is exported. We can also use T()function to save the result set as an Excel file. Then A6’s code will be >T(“EmpBirthday.xlsx”:A5;“Daily Statistics”), which automatically identifies the file format according to extension and exports A5’s data to the Excel file.

4. Reverse pivot and sorting by the specified order
Contents and Exercise Data