14.5 Make up missing parts to make data continuous

 

The following table records the daily sales data of products, and some dates are missing due to no sales data:

imagepng

Now we want to list the daily sales data in the order of date, and make up the dates missed in the original table, as shown in the following figure:

imagepng

Script:

A
1 =E(‘A1:C11’)
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)

A2: Find the minimum date in A1
A3: Find the maximum date in A1
A4: Concatenate all dates from the minimum date to the maximum date in order into a sequence
A5: Align the SaleDate of A1 in the order of A4
A6: Use the function new to reassign each row of A5, # represents the current row number of A5, take the date value of the same row number in A4 as SaleDate, and then take the Product and Amount columns of the current row of A5

esProc Desktop and Excel Processing
14.4 Expand one row into multiple rows after splitting text
14.6 Add several blank rows every N rows