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