...

Fill in missing parts in continuous values

 

Example

There is a daily sales record table sales.xlsx, some dates do not have sales records, and some of the data is shown in the figure below:

The daily sales data need to be listed in order of date, and the dates without sales data should also be listed, as shown in the following figure:

Write SPL script:

A

1

=T("e:/work/sales.xlsx")

2

=A1.min(saledate)

3

=A1.max(saledate)

4

=A2|(A3-A2).(A2+~)

5

=A1.align(A4,saledate)

6

=A5.new(A4(#):saledate,product,amount)

7

=T("e:/work/sales1.xlsx",A6)

A1 Read in the sales.xlsx file data 

A2 Find the smallest date in A1 

A3 Find the maximum date in A1 

A4 Combine all dates from the smallest date to the largest date in order to form a sequence 

A5 Align the saledate of A1 in the order of A4 

A6 Use the new function to create each row of A5, # represents the current row number of A5, take the date of the same row number in A4 as the saledate, and then take the product and amount columns of the current row of A5 

A7 Save the results in A6 to the file sales1.xlsx

 
 •  259 View  •