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:
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:
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
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
