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:

  WHEN "Species1" > 0 THEN 1
  ELSE 0

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

2 Answers 2


You can use the expression:

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.

enter image description here


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.

enter image description here

  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_filter (
                map_akeys( attributes()),
                regexp_match( @element,'Species')
        @element > 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.