Loop through fields in QGIS calculator with condition: count fields with values >0

Question

I have a dataset, a polygon, with several columns, let's say:

Site	Species1	Species2	Species3
1	85	23	90
2	73	20	0
3	0	0	0
4	0	0	70
5	0	75	60

I have different sites and data about several species, each is a % of the surface covered by that species. I wrote three sample columns with data about species but it is actually hundreds.

What I need is an if function that creates a 4th column adding the total amount of species present in each site, assuming that if Speciesn=0 the species is absent and Speciesn>0 the species is present. For each column I need something like:

CASE 
  WHEN "Species1" > 0 THEN 1
  ELSE 0
END

But I definitely need a loop to iterate through columns!

So the resulting column should look like this:

Site	Species1	Species2	Species3	Total
1	85	23	90	3
2	73	20	0	2
3	0	0	0	0
4	0	0	70	1
5	0	75	60	2

Answer 1

You can use the expression:

array_length(array:=
array_filter(array:=
array_foreach(array:=generate_series( 1,10,1),
expression:=attributes()[concat('Species', @element)]), expression:=@element > 0))

Generate series will generate the numbers from 1 to 10 (change 10 to the maximum of your field numbers, I only have 10).

Then array foreach will iterate over each number and combine Species+number (into the fieldname) and attributes will fetch the value of that field and create an array.

Array filter will extract the numbers in the array which are above 0.

Then array length will count the remaining elements in the array.

Answer 2

A different version of the solution by @Bera that works automatically for all fields that contain the textstring Species, regardless if you have 3 or 3000 such fields, and filters out all other fields.

Another adavantage: because the filtering is based on Regular Expressions, you could add further filter criteria - e.g. if you have field names containing Species that you don't want to include (like Species_remarks), you could limit the selection to fields named Species followed by a number, changing line 6 below to regexp_match( @element,'Species\\d'). This will only select fields named Species1, Species2, ... Species3875 etc.

1. Create an array of all fieldnames with attributes() (creates a key:value map of all fields) and map_akeys() (get the keys from this map as an array).
2. Filter this array to keep only fieldnames containing Species with array_filter() and regexp_match().
3. Then loop through all these fields with array_foreach()
4. Inside the loop, get the value of each fieldname, again using attributes() with map_get(). You get an array with the values of all fields that contain Species in their name.
5. Count how many of these values are >0 with array_filter().

array_length(
    array_filter (
        array_foreach(
            array_filter(
                map_akeys( attributes()),
                regexp_match( @element,'Species')
            ),
            map_get(
                attributes(),
                @element
            )
        ),
        @element > 0
    )
)