# Computing ratio based on diferent columns on QGIS calculator

I need help regarding QGIS calculator: I have a dataset, a polygon, with several columns:

Rowid Pond1 Pond2 Pond2 Lake1 Lake2 Lake3 Stream1 Stream2 Stream3
1 JA JA BA BA WC WC WC
2 WC FA JA FA WC
3 JA BA BA WC
4 BA WC JA FA WC BA
5 JA FA JA FA BA

This a dataset of scientific surveys of different aquatic habitats. Each row is an area and in each area we have 3 different types of habitats: ponds, lakes and streams. In each area (row) we can have `n` sites of each type with different people in charge of the work. In this dataset I wrote pond 1, 2 and 3 but it is actually many more than 3 in each type. Our objective is to have an optimal coverage of 2 for each habitat and each area (row), so the best would be 6/6. I can calculate a proportion that could be actually bigger than that, but I need to limitate the proportion to a maximum of 6/6 where each kind of habitat can only contribute a maximum of 2/6. something like:

Rowid Pond1 Pond2 Pond2 Lake1 Lake2 Lake3 Stream1 Stream2 Stream3 Cover
1 JA JA BA BA WC WC WC 6/6
2 WC FA JA FA WC 4/6
3 JA BA BA WC 3/6
4 BA WC JA FA WC BA 4/6
5 JA FA JA FA BA 4/6

So I need a chunk of code for QGIS calculator to calulate that proportion, with a maximum of 6/6 where each haitat can only contribut 2/6, giving that the name of the responsibles of the places and habitats that fill the table can vary a lot, including spaces and weird characters, and that the number of columns in each habitat is also variable (Pond1, Pond2, Pond3, Pond4...).

• You should consider a different data structure: one to many (1-n)... where you have one area with n habitats and the habitat type is an attribute in the related table. Then you can use the relation aggregate function of the QGIS field calculator. IMO it would be much more easier and flexible... Commented Jan 10 at 10:12
• I don't really understand how you calculate, what e.g. 6/6 in the first row means and based on what/how you calculate it... Commented Jan 10 at 11:20
• Yes it's not easy to understand. There is 3 types of sites: pond, lake and stream. For each habitat in each zone (which are rows) we can have n ponds, n lakes, n streams. Our objective is to have 2 ponds, 2 lakes and 2 streams, which woudl be 6/6. What happens is that we may have 3 ponds, 2 lakes and 2 streams. In this case, the proportions would be 7, but we need to have, also there, a 6/6. Commented Jan 10 at 12:15

Use the following expression. It calculates how many field that contain `pond` in the fieldname have a textstring value. If there are more then two appearances, just the number 2 is retained.

The same is done for all fieldnames that contain `lake` and `stream`. Finally, the sum of these three values is created. It does not depend how many fields you have: just 1, five or several hundred. Also, the number of fields for all three water types can be different. Thes expression automatically handles all this correctly.

Like e.g.: 5 times any non NULL value in a field with fieldname containing `pond` returns 2 ( 5 > 2, so only 2 are retained) and the same for the two other fieldnames, e.g.: 1 time `lake` returns 1, 0 times `stream` returns 0, thus: `2+1+0 = 3`.

Screenshot, showing how the expression works: output of the expression in the field `cover`, here exemplified for feature (row) 3: 2 ponds + 3 lakes + 1 stream -> 2 + 2 + 1 = 5:

``````array_sum(
array_foreach(
array_foreach (
array('pond','lake','stream'),
with_variable(
'nm',
@element,
array_length(
array_filter (
array_foreach(
array_filter(
map_akeys(attributes()),
regexp_match( @element,@nm)
),
map_get(
attributes(),
@element
)
),
@element>0
)
)
)
),
if(
@element > 2,
2,
@element
)
)
)
``````