SPL：Complicated Static Transposition

The transposition function is commonly presented in front-end such as reports, which transposes the queried data into a specific display layout. For example, row-to-column transposition, column-to-row transposition, more complex dynamictransposition, etc. In SPL: Static Transposition , we have introduced the static transposition. Sometimes, the structure of the transposed table is definite, but it can hardly be performed with the pivot function directly. So let’s take a look at how SPL handles such complicated static transposition.

1. Multirow-to-multirow transposition

[Example 1] Based on the following punch-in data table, generate a new table recording the daily activities of each employee. Each person corresponds to seven records per day:

PER_CODE	IN_OUT	DATE	TIME	TYPE
1110263	1	2013-10-11	09:17:14	In
1110263	6	2013-10-11	11:37:00	Break
1110263	5	2013-10-11	11:38:21	Return
1110263	0	2013-10-11	11:43:21	NULL
1110263	6	2013-10-11	13:21:30	Break
1110263	5	2013-10-11	14:25:58	Return
1110263	2	2013-10-11	18:28:55	Out
…	…	…	…	…

The layout of the target table is expected to be as follows:

PER_CODE	DATE	IN	OUT	BREAK	RETURN
1110263	2013-10-11	9:17:14	18:28:55	11:37:00	11:38:21
1110263	2013-10-11	9:17:14	18:28:55	13:21:30	14:25:58
…	…	…	…	…	…

Although the structure of the transposed table is definite, it is still complicated to get the operation done using the A.pivot() function. In this case, we can create the target data structure and then fill it with specific data. First of all, we create an empty table according to the target structure. Then the data is sorted with every 7 records in one group, and the members in each group are [1,7,2,3,1,7,5,6], i.e., the time values of 2 records to be generated. Lastly, we fill the data in the table according to the target structure.

The SPL script is as follows:

	A
1	=create(PER_CODE,DATE,IN,OUT,BREAK,RETURN)
2	=T("DailyTime.txt").sort(PER_CODE,DATE,TIME)
3	=A2.group((#-1)\7).(~([1,7,2,3,1,7,5,6]))
4	>A1.record(A3.conj([~.PER_CODE,~.DATE]|~.(TIME).m([1,2,3,4])|[~.PER_CODE,~.DATE]|~.(TIME).m([5,6,7,8])))

A1: Create an empty table according to the target structure.

A2: Import the daily punch-in records and sort them according to the employee code and date.

A3: Group every 7 records, and in each group, return records in the specified order ([1,7,2,3,1,7,5,6]).

A4: Concatenate all the returned records according to the target order and insert them into the table created in A3.

2. Inter-row calculation during row-to-column transposition

[Example 2] Based on the following user payment detail table, generate a new table storing the payable amount per month for each user in the year 2014. Below is part of the source table:

ID	CUSTOMERID	NAME	UNPAID	ORDER_DATE
112101	C013	CA	12800	2014/02/21
112102	C013	CA	3500	2014/06/15
112103	C013	CA	2600	2015/03/21
112104	C025	BK	4600	2014/06/11
112105	C002	TUN	23100	2014/01/22
112106	C002	TUN	13800	2014/08/03
…	…	…	…	…

The layout of the target table is expected as follows:

NAME	1	2	3	4	5	6	7	8	9	10	11	12
TUN	23100	23100	23100	23100	23100	23100	23100	13800	13800	13800	13800	13800
CA		12800	12800	12800	12800	3500	3500	3500	3500	3500	3500	3500
BK						4600	4600	4600	4600	4600	4600	4600
…

As for such complicated static transposition, we still create the target data structure and then fill it with specific data.

The SPL script is as follows:

	A
1	=create(NAME,${12.concat@c()})
2	=T("UserPayment.txt").select(year(ORDER_DATE)==2014).group(CUSTOMERID)
3	>A2.((m12=12.(null),~.(m12(month(ORDER_DATE))=UNPAID), m12.(~=ifn(~,~[-1])),A1.record(NAME|m12)))

A1: Create an empty table according to the target structure.

A2: Import the user payment table containing records of the year 2014 and sort it by customer ID.

A3: Loop through each group, then each member in the group to calculate the payable amount of each month, and insert the result to the table created in A1 with customer names.

3. List data in horizontal columns

Listing data in horizontal columns is often used for data presentation, where data with the same type of attributes is listed in the same columns for convenient viewing and comparing.

[Example 3] List the names and salaries of employees who get paid over 10,000 in both sales and R&D departments (the records are sorted from the largest to smallest in each column). Part of the employee table is shown below:

ID	NAME	SURNAME	STATE	DEPT	SALARY
1	Rebecca	Moore	California	R&D	7000
2	Ashley	Wilson	New York	Finance	11000
3	Rachel	Johnson	New Mexico	Sales	9000
4	Emily	Smith	Texas	HR	7000
5	Ashley	Smith	Texas	R&D	16000
…	…	…	…	…	…

The expected result is as follows:

SALESNAME	SALARY	RDNAME	SALARY
Madeline	15000	Ashley	16000
Jacob	12000	Jacob	16000
Andrew	12000	Ryan	13000
…	…	…	…

SPL lists data in horizontal columns in a similar way as the previous examples. We still create the target data structure and then fill it with data.

The SPL script is as follows:

	A
1	=T("Employee.csv").select(SALARY >10000).sort@z(SALARY)
2	=A1.select(DEPT:"Sales")
3	=A1.select(DEPT:"R&D")
4	=create('SALESNAME',SALARY,'RDNAME', SALARY)
5	=A4.paste(A2.(NAME),A2.(SALARY),A3.(NAME),A3.(SALARY))

A1: Import the employee table, select the records with a salary over 10,000, and sort them by salary in descending order.

A2: Get records of the sales department.

A3: Get records of the R&D department.

A4: Create an empty table according to the target structure.

A5: Use the A.paste() function to paste the result values to corresponding columns.

DailyTime.txt

Employee.csv

UserPayment.txt