...

8.13 Split and expand similar attributes in same row to multiple rows

 
spl-cookbook(224)

We have a database table TBLORDERS. Below is part of its data:

ORDERNUMBER STEP1_STATUS STEP1_START STEP1_END STEP2_STATUS STEP2_START STEP2_END
1 COMPLETE 2020-01-01 2020-02-01 INPROCESS 2020-01-15 2020-02-01
2 COMPLETE 2020-01-01 2020-02-01 INPROCESS 2020-01-15 2020-02-01

The original table is wide. The above only shows its seven columns. Our computing goal is to split and expand similar attributes in a same row to multiple rows, as shown below:

ORDERNUMBER STEP_NUMBER STEP_STATUS STEP_START STEP_END DURATION_IN_DAYS
1 STEP1 COMPLETE 2020-01-01 2020-02-01 31
1 STEP2 INPROCESS 2020-01-15 2020-02-01 17
2 STEP1 COMPLETE 2020-01-01 2020-02-01 31
2 STEP2 INPROCESS 2020-01-15 2020-02-01 17

The process is like this: insert STEP[n] to cells under STEP_NUMBER column and enter corresponding values to STEP[n]_STATUS, STEP[n]_START and STEP[n]_END columns respectively in the result table while calculating the difference between date of STEP[n]_END and date of STEP[n]_START.

SPL script:

A
1 =connect(“MSSQL”).query@x(“SELECT * FROM TBLORDERS”)
2 =create(ORDERNUMBER,STEP_NUMBER,STEP_STATUS,STEP_START,STEP_END,DURATION_IN_DAYS)
3 =((A1.fno()-1)\3).(“A2.insert(0,A1.ORDERNUMBER,"STEP”/#/“",STEP”/#/“_STATUS,STEP”/#/“_START,STEP”/#/“_END,interval(STEP_START,STEP_END))”).concat@c()
4 >A1.run(${A3})
5 return A2

A1 Connect to the database to retrieve data.
A2 Generate data structure of the result set.
A3 Piece together an expression dynamically: besides the first column in A1, make every 3 ones in the 3n columns after it correspond a step (STEP), insert the step’s content in order (STEP[n]）, STEP[n]_STATUS, STEP[n]_START, STEP[n]_END, and difference between STEP[n]_END, and STEP[n]_START) into A2, and repeat the process for n times.
A4 Execute the dynamic expression in A3.

spl-cookbook(224)
 
 •  149 View  •