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

Convert one row into multiple rows based on the value

 

Example

There is a product quantity table in the Excel file book1.xlsx, the first column is the product serial number, and the second column is the remaining quantity of the product. The initial data are as follows:

According to the remaining quantity of the product Qty Remaining, repeat the value of Item ID n times, except for the original row in the first row, the other rows only retain the value of Item ID, and the number of 00003 is 0, then there is no longer this record in the new table , The expected result is as follows:

Write SPL script:

A

1

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

2

=A1.news(#2;'Item   ID',if(#==1,'Qty Remaining',null):'Qty Remaining',if(#==1,'Invoice   No',null):'Invoice No')

3

=T("E:/work/book2.xlsx",A2)

A1 Read the book1.xlsx file 

A2 According to the number of the second column, repeat each record n times to generate a new table sequence, where the values of the second and third columns use the if expression, the original value is used for the first row, and the null value is used for the other generated new rows 

A3 Save A2 to the file book2.xlsx

 
 •  466 View  •