7.10 Add records for missing values in the grouped and summarized result according to table association
We have the following data:
| Date | Sales |
|---|---|
| 2022/5/3 | 88818 |
| 2022/5/4 | 70964 |
| 2022/5/6 | 19370 |
| 2022/5/7 | 37837 |
| 2022/5/6 | 17512 |
| 2022/5/3 | 36062 |
| 2022/5/4 | 96110 |
Our task is to calculate the total sales of each day from 2022-05-03 to 2022-05-07; record the sales as 0 if there isn’t sales for a certain day.
Note: There is missing data in the table. No sales data for the date 2022-05-05.
In this case, it is not right to perform grouping & aggregation directly. Instead, we need to generate a sequence of dates from 2022-05-03 to 2022-05-07, ensuring that each date is included, and perform alignment aggregation according to the date sequence.
SPL script:
| A | |
|---|---|
| 1 | =T(“data.xlsx”) |
| 2 | =periods(“2022-05-03”, “2022-05-07”) |
| 3 | =A1.align@a(A2,Date) |
| 4 | =A3.new(A2(#):Date, ~.sum(Sales):Sales) |
A3 Align A1’s records to A2 according to values of Date field.
A4 Create the result table sequence by aligning to both A3’s result set and A2’s sequence.
Execution result:
| Date | Sales |
|---|---|
| 2022-05-03 | 124880 |
| 2022-05-04 | 167074 |
| 2022-05-05 | |
| 2022-05-06 | 36882 |
| 2022-05-07 | 37837 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
