RaqForum 22 No.
1 Reply  •  429 View  • 
     ...

Convert certain columns of the same row into multiple rows as classification members

 

Example

There is a cross table book1.xlsx, and part of the data is shown in the figure below:

Turn it into a row-stayle table, as shown in the following figure:

Write SPL script:

A

1

=T("E:/pivot/book1.xlsx")

2

=A1.pivot@r(Names,Sales;Weekday,Day)

3

=A2.new(Names,Sales,Day).select(Day)

4

=T("E:/pivot/book2.xlsx",A3)

A1 Read in the book1.xlsx file 

A2 Transpose the data of A1, the option @r means column to row, with Names and Sales as grouping, other column names are converted to Weekday column values, and other column values are converted to Day column values. 

A3 Select the records in the three columns of Names, Sales, and Day in A2 and the Day is not empty 

A4 Save the results in A3 to the file book2.xlsx

 
 •  429 View  •