5.4 Split by row - segment by data (by filtering condition)

 

There is an order detail data file OrderDetailExtended.xlsx as follows:

imagepng

Now we want to split the data of this table into three segments by the value in the column of ExtendedPrice (<500, 500-2000, >2000), and save the segmented data as three Excel files. The split results are as follows:

lt500.xlsx:

imagepng

Mt2000.xlsx:

imagepng

500-2000.xlsx:

imagepng

Script:

A B
1 =T(“OrderDetailsExtended.xlsx”)
2 =A1.group(if(ExtendedPrice<500:“lt500.xlsx”,ExtendedPrice>2000:“mt2000.xlsx”;“500-2000.xlsx”):fileName;~:data)
3 for A2 =T(A3.fileName,A3.data)

A1: Read the data in OrderDetailsExtended.xlsx.

A2: Divide A1 into three groups by the value in the column of ExtendedPrice (i.e., value less than 500, value greater than 2000, and value between 500 and 2000), and take such values as the name of corresponding file.

A3: Loop by A2.

B3: Write the group data in each row to the corresponding file.