...

5.11 Application: Complex inter-row computations

 
spl-cookbook(224)

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-cookbook(224)
 
 •  139 View  •