#79 - Split Aggregation Values And Fill Them in Detail Rows

#programming #beginners #tutorial #productivity

Problem description & analysis:

We have an annual and monthly water consumption data table for the water meter and part of the data is shown below:

We also have a statistical table for annual water leakage amount:

Task: Now we want to assign the annual water leakage amount to the Water leakage column of the first table according to the proportion of monthly water consumption in the total water consumption of the year (to calculate in cell D1).

Solution:

Use SPL XLL and enter the following code:

 A
1 =E(‘A1:C44’).derive(‘Water leakage’)
2 =E(‘Sheet2!A1:B5’)
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 return A1.new(‘Water leakage’)

A3: Group the data in A1 by Year.
A4: Loop through every group in A3; the variable a is the leakage loss of the corresponding year selected from A2; the variable s is the total Water consumption in this year, and then loop through all rows in this year; assign the Water leakage column as Water*a/s.
A5: Return to A1.

The results are as follows: