5.11 Application: Complex inter-row computations
Group and summarize data, and in each group calculate values for the target columns and perform inter-row computations.
Calculate the amount payable in each month of the year 2014 for every user based on the user payment detail table. Below is part of the table:
| ID | customID | name | amount_payable | due_date | amount_paid | pay_date |
|---|---|---|---|---|---|---|
| 112101 | C013 | CA | 12800 | 2014-02-21 | 12800 | 2014-12-19 |
| 112102 | C013 | CA | 3500 | 2014-06-15 | 3500 | 2014-12-15 |
| 112103 | C013 | CA | 2600 | 2015-03-21 | 6900 | 2015-10-17 |
The requirement is to output the amount payable in each month of the specified year (like 2014), and, if there isn’t data for the current month, the amount payable of the current month is that of the previous month:
| name | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CA | 12800 | 12800 | 12800 | 12800 | 16300 | 16300 | 16300 | 16300 | 16300 | 16300 | 16300 | |
| … |
SPL script:
| A | B | |
|---|---|---|
| 1 | =T(“Payment.txt”).select(year(due_date)==2014) | |
| 2 | =create(name,${12.().concat@c()}) | =A1.group(customID) |
| 3 | for B2 | =12.(null) |
| 4 | >A3.run(B3(month(due_date))= amount_payable) | |
| 5 | >B3.run(~+=~[-1]) | |
| 6 | =A2.record(B2.name|B3) | |
A1 Import data of the year 2014.
A2 Generate an empty table containing 12 months.
B2 Group A1’s data by customer ID.
B3 Generate empty data for the 12 months.
B4 Set the amount payable for each month.
B5 Set the null value as the amount payable in the previous month and perform cumulative sum when new amount is added.
B6 Insert records to the result table.
| name | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CA | 12800 | 12800 | 12800 | 12800 | 16300 | 16300 | 16300 | 16300 | 16300 | 16300 | 16300 | |
| … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
