2.18 Iterative aggregation: User-defined iteration termination criterion
Programmers can set their own computing expression and termination criterion when performing an iterative calculation.
One task is to find the day when the sales target of 150,000 in the first quarter of the year 2014 is reached based on the following sales table.
| OrderID | Customer | SellerId | OrderDate | Amount |
|---|---|---|---|---|
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =T(“sales.xlsx”) |
| 2 | =A1.select(year(OrderDate)==2014) |
| 3 | =A2.iterate((@+=Amount, ~~=OrderDate),0,@>150000) |
A1 Load data table.
A2 Get records of the year 2014.
A3 Use iterate ()function to perform iteration with default value being 0. The computation adds up sales amounts cumulatively from beginning to the current cell until the amount is above 150,000. The iterate() function returns the eligible date.
Execution result:
| Value |
|---|
| 2014/3/25 |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
