SPL: Dynamic Transposition

 

The transposition function is commonly presented in the 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. Dynamic row-to-column transposition means the fields generated during transposition can not be specified in advance, but can only be determined dynamically according to the values of the original fields. So let’s take a look at how SPL handles the dynamic transposition.

1. Automatically generate a column according to field values

[Example 1] Based on the employee table, calculate the average salary of each department in different areas. Some of the data 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 layout of the target table is expected to be as follows:

DEPT

California

Colorado

Florida

Administration



9333.333

Finance

8000

5000

10000

HR

10000


7000

The row-to-column transposition needs to get its target fields from the original table. The A.pivot() function in SPL can do such a transposition, which will automatically extract names of the target fields when no target fields are specified.

The SPL script is:


A

1

=T("Employee.csv")

2

=A1.groups(STATE,DEPT;avg(SALARY):AVG_SALARY)

3

=A2.pivot(DEPT; STATE, AVG_SALARY)

A1: Import the employee table.

A2: Group the table by STATE and DEPT to calculate the average salary of each department in each state.

A3: Use the A.pivot() function to transpose rows to columns, and retrieve the names of target fields automatically when they are not specified.

2. Dynamically generate column names through calculation

[Example 2] Based on the income details table, get the income information of each employee with categories generated automatically. Below is part of the source table:

NAME

SOURCE

INCOME

David

Salary

8000

David

Bonus

15000

Daniel

Salary

9000

Andrew

Shares

26000

Andrew

Sales

23000

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

NAME

SOURCE1

INCOME1

SOURCE2

INCOME2

Andrew

Shares

26000

Sales

23000

Daniel

Salary

9000



David

Salary

8000

Bonus

15000

Robert

Bonus

13000



The target fields are not retrieved from a certain field dynamically but need to be calculated dynamically. Here the A.pivot() function is no longer available. We can generate the target data structure according to the group with the most income categories, and then fill it with data.

The SPL script is:


A

1

=T("Income.txt").group(NAME)

2

=A1.max(~.len())

3

=create(NAME, ${A2.("SOURCE"/~/", INCOME"/~).concat@c()})

4

>A1.(A3.record(~.NAME | ~.conj([SOURCE, INCOME])))

A1: Import the income details table, and group it by NAME.

A2: Calculate the maximum number of members in groups, i.e., the largest number of income categories.

A3: Generate column names dynamically according to the result in A3, and create an empty table.

A4: Loop through each group, and fill in the names, income sources, and amounts with the table created in A3.

3. Dynamic row-to-column transposition through table join

[Example 3] Based on the following ORDERS table, ORDER_DETAIL table, and PRODUCT table, generate a table recording information of products purchased each day by each customer in the year 2014. The relationship between ORDERS table and ORDER_DETAIL table is one to many, where each order corresponds to multiple order detail records. The relationship between ORDER_DETAIL table and PRODUCT table is many to one, where the product ID field of ORDER_DETAIL table points to the ID field of PRODUCT table. Below are parts of the three tables:

ORDERS:

ID

CUSTOMERID

EMPLOYEEID

ORDER_DATE

ARRIVAL_DATE

10248

VINET

5

2012/07/04

2012/08/01

10249

TOMSP

6

2012/07/05

2012/08/16

10250

HANAR

4

2012/07/08

2012/08/05

10251

VICTE

3

2012/07/08

2012/08/05

10252

SUPRD

4

2012/07/09

2012/08/06

ORDER_DETAIL:

ID

ORDER_NUMBER

PRODUCTID

PRICE

COUNT

DISCOUNT

10814

1

48

102.0

8

0.15

10814

2

48

102.0

8

0.15

10814

3

48

306.0

24

0.15

10814

4

48

102.0

8

0.15

10814

5

48

204.0

16

0.15

PRODUCT:

ID

NAME

SUPPLIERID

CATEGORY

1

Apple Juice

2

1

2

Milk

1

1

3

Tomato sauce

1

2

4

Salt

2

2

5

Sesame oil

2

2

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

ORDER_DATE

CUSTOMERID

PRODUCT1

COUNT1

PRODUCT2

COUNT2

2014/1/5

VICTE

Corn flakes

8




2014/1/23

CONSH

Chicken

3

Cake

9


We can first join up the three source tables through their associative relationships and perform the subsequent calculations using the same method in the previous two examples.

The SPL script is:


A

1

=T("Orders.txt").select(year(ORDER_DATE)==2014)

2

=T("Product.txt")

3

=T("OrderDetail.txt").switch(PRODUCTID,A2:ID).group(ID)

4

=join(A1:ORDERS,ID;A3:DETAIL,ID)

5

=create(DATE,CUSTOMERID,${A4.max(DETAIL.len()).("PRODUCT"/~/","/"COUNT"/~).concat@c()})

6

>A4.run(A5.record([ORDERS.ORDER_DATE,ORDERS.CUSTOMERID]|DETAIL.([PRODUCTID.NAME,COUNT]).conj()))

A1: Import the Orders table and select records of the year 2014.

A2: Import the Product table.

A3: Import the OrderDetail table, join it with the Product table through the PRODUCTID filed, convert the PRODUCTID filed values to corresponding product records, and then group the joining result by order ID.

A4: Join up Order table with OrderDetail table through order ID.

A5: Generate the target data structure according to the group with the most OrderDetail records, and create an empty table.

A6: Loop through the order data and fill them in the table created in A5 in turn.

In fact, this case is the same as the previous ones essentially with only one more step, that is, joining up the three tables through their associative relationships. The subsequent operations are the same, creating the target data structure as needed and filling it with data.

Employee.csv

Income.txt

OrderDetail.txt

Orders.txt

Product.txt