I've got a list of sewer subcatchments with an ID field and 12 numeric columns called area_absolute_1 to area_absolute_12. But only some of those 12 are used; the other columns are all zeroes.
subcatchment_number area_absolute_1 area_absolute_2 area_absolute_3 area_absolute_4 area_absolute_5 area_absolute_6 area_absolute_7 area_absolute_8 area_absolute_9 area_absolute_10 area_absolute_11 area_absolute_12
1 0.439 0.277 0 0 0 0 0 0 0.054 1.54 0 0
2 0 0 0 0 0 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0 0 0
4 0 0 0 0 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0 0 0 0 0
6 0.228 0.334 0 0 0 0 0 0 0.026 0.172 0 0I also have a small lookup table that tells me which columns are in use, and what these columns represent:
Surface Number Surface Type
Surface 1 Road
Surface 2 Roof
Surface 3 -
Surface 4 -
Surface 5 -
Surface 6 -
Surface 7 -
Surface 8 -
Surface 9 Road
Surface 10 Permeable
Surface 11 -
Surface 12 -The last column can only have values "Road", "Roof" or "Permeable"; but each of these can exist more than once (the maximum I have seen is three).
I now need to know the total area of roads, roofs, and permeable area in each subcatchment. For roads, it would be summing columns "area_absolute_1" and "area_absolute_9"; for roofs, just column "area_absolute_2", and for permeable, just column "area_absolute_10".
How do I join the fieldname lookup table to the data table, so that I can calculate the totals for each of the three surface types?
I need to do this hundreds of times. The datasets always have those same 12 columns, but the surface type mapping varies. Therefore I can't do any hard-coding of column names.
Attached is template which kind of works, but it seems an onerous way to go about it, and I feel there must be a simpler, more elegant way?
