RaqForum 25 No.
253 View  • 
     ...

8.7 Row-to-column transposition with location-based value assignment

 
spl-cookbook(224)

This type of transposition generates data structure of the target table sequence dynamically and assigns values to the table according to row/column location.
One example scenario is to dynamically generate the user column according to user records and get each user’s logged-in information. Here the task is to summarize users action information in each week of the year 2018 according to the associated User table and Record table.

User
ID
Name
Record
ID
Date

The goal is to get records displaying whether each user performs operations in each week in the year 2018. The desired result is as follows:

Week User1 User2 User3
1 Yes No Yes
2 Yes Yes No
3 Yes No Yes
4 No Yes Yes

First, we create the target data structure and then fill data in it.

SPL script:

A
1 =connect(“db”).query@x(“select t1.ID as ID, t1.Name as Name, t2.Date as Date from User t1, Record t2 where t1.ID=t2.ID and year(t2.Date)=2018”)
2 =A1.derive(interval@w(“2018-01-01”,Date)+1:Week)
3 =A2.group(ID)
4 =A2.max(Week)
5 =A4.new(~:Week,${A3.(“"No":”+Name).concat@c()})
6 =A3.run(~.run(A5(Week).field(A3.#+1,“Yes”)))

A1 Query User table and Record table and join them on user ID.
A2 Find the ordinal number of the week according to Date value and store result in the new field Week.
A3 Group A2’s records by user ID.
A4 Get the largest ordinal number of the week.
A5 Create an empty table sequence according to the largest week ordinal number and assign “No” as default values.
A6 Locate the corresponding record in the target table for each piece of data in each group according to ordinal number of the week, and replace default value with “Yes”.

 

spl-cookbook(224)
 
 •  253 View  •