13.4 Interconversion of upper layer groups for rows and columns - column-to-row
Here below is a data table:
The first row is the country code of each piece of data, the second row is other column names of each piece of data, and the third row is the value corresponding to each column. Now we want to rearrange the data into standard row-based data. The result is as follows:
Script:
| A | |
|---|---|
| 1 | =transpose(‘Sheet1!A1:I3’).run(if(~(1)==null,~(1)=~[-1](1))) |
| 2 | =create(Country,Cate,Value) |
| 3 | >A1.(A2.record(~)) |
| 4 | =A2.pivot(Country;Cate,Value) |
A1: Transpose the sequence of sequences, and complement the countries in the first column
A2: Create an empty table sequence (Country,Cate,Value)
A3: Fill in the data of A1 in the table sequence one by one
A4: Use the pivot function to perform row-to-column conversion
https://c.scudata.com/article/1651916536524
https://c.scudata.com/article/1658065251244
https://c.scudata.com/article/1658111369913
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.esproc.com
Discord 👉 https://discord.gg/2bkGwqTj
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
