Solved

How to join a fieldname lookup


Badge +3

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 0

I 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?

SurfaceMapping

icon

Best answer by nielsgerrits 11 June 2021, 19:44

View original

2 replies

Userlevel 6
Badge +31

I would use an AttributeExploder to explode the data into features, merge the lookup table and rebuild the data using an AttributeCreator and an Aggregator. Workspace attached.

Badge +3

I would use an AttributeExploder to explode the data into features, merge the lookup table and rebuild the data using an AttributeCreator and an Aggregator. Workspace attached.

Dankjewel @nielsgerrits​. That's perfect: it's much neater and tidier than my initial attempt. And also takes care of the scenario when there are more than 3 surface types mapped as "Road" (which initially I thought wasn't possible!)

Reply