"We have an annual and monthly water consumption data table for water meter, and part of the data .."

mars RaqForum 25 No.
237 View • 1 Years ago

7.4 Split aggregation value and fill them in detail rows

desktop and excel data processing cases(180)

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

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

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 (calculate in cell D1):

	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.

imagepng