...

8.8 Transpose rows to columns while performing cross-column computations

 
spl-cookbook(224)

Transpose rows to columns while performing cross-column computations.
Create a summary table recording monthly amount each user needs to pay in the year 2014 based on the following payment detail 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

The computing rule is to output the monthly amount each user needs to pay in the specified year (like 2014). If there is no payment data for the current month, the amount to be paid in this month is the same as the that in the previous.

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
… … … … … … … … … … … … …

To achieve the computing goal, we first create an empty result set and then insert data to it. We need to perform a series of computation to obtain data to be inserted.

SPL script:

A B
1 =T(“Payment.txt”).select(year(due_date)==2014)
2 =create(name,${12.().concat@c()}) =A1.group(customerID)
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 from Payment file.
A2 Generate an empty table sequence having columns of 12 months.
B2 Group the imported data by customerID.
A3~B6 Perform grouping in loop, during which B4 sets amount to be paid for each month.
B5 Assign the to-be-paid amount of the previous month to each null value and add the newly-increased amount to it if there is any.
B6 Insert result records to the result table sequence.

spl-cookbook(224)
 
 •  147 View  •