...

Split Summary Values of Groups into the Detail Rows

 

Problem description

The Excel file book1.xlsx stores the water consumption of each year and month, and some of the data is shown in the figure below:

..

In addition, the annual water leakage summary table leakage.xlsx is shown in the figure below:

..

We need to allocate the annual water leakage to the Water leakage column of each year and month according to the ratio of the monthly water consumption in each year to the total water consumption of the year.

Write SPL script:

A

1

=T("E:/work/book1.xlsx")

2

=T("E:/work/leakage.xlsx")

3

=A1.group(Year)

4

=A3.run(a=A2.select@1(Year==A3.Year).'Water   leakage',s=~.sum(Water),~.run('Water leakage'=Water*a/s))

5

=T("E:/work/book2.xlsx",A1)

A1 Read the data of book1.xlsx

A2 Read the data of leakage.xlsx

A3 Group A1 data by Year

A4 Loop through each group in A3, variable a is the water leakage of the corresponding year selected from A2, variable s is the total water consumption of the current year, and then loop through all the records in the current year, and assign the value “Water*a/s” to the Water leakage column.

A5 Store A1 to book2.xlsx


 
 •  295 View  •