2

I am working on a file with 2 related layers: Developments and Projects (1 to many relationship). The purpose is to track renewable projects in my country where one "Development" can contain several "Projects". This way I can track that Development Langa has 3 projects: Langa Solar Energy Facility, Langa Wind Energy Facility and Thezi-Langa Solar Energy Facility.

I have a calculated field in the Developments layer that returns the number of projects in each development by using the following expression:

relation_aggregate( relation:= 'Projects', aggregate:= 'count', expression:= "fid" )

I would like to add 2 extra fields that will do the same, but technology specific: one will count solar projects and the other will count wind projects according to the "technology_id" in the Projects layer. In the example described above for the Langa development, it would return 2 for the calculated field for solar and 1 for the calculated field for wind. Essentially, I want the functionality of the COUNTIF function of MS Excel.

Improve this question

1 Answer 1

Reset to default
4

As the relation_aggregate appears to have no filter parameter, you can adapt your expression like so:

QGIS >=3.28
array_count( relation_aggregate( relation:='Projects', aggregate:='array_agg', -- make array of values returned by the `expression` parameter expression:="technology" -- the field name containing the technology attribute ), 'solar' -- the attribute value of the name/code of the technology to be counted )

It aggregates all technology's into an array (per relation), and then counts all instances of solar in that array.

QGIS <=3.26
array_length( array_filter( relation_aggregate( 'Projects', 'array_agg', "technology" ), @element='solar' ) )

It aggregates all technology's into an array (per relation), filters that array for instances of solar only, and then gets the length.

You can repeat the expression for your second field but change solar for wind.

Output before the count

note, screenshots are for QGIS >=3.28

Shown here are the technologies in the array for Development A:

enter image description here

Output with the count included

Shown here is the number of solar projects for Development A:

enter image description here

Final result

enter image description here

Multiple conditions

To count instances using multiple conditions, the expression parameter of relation_aggregate can be set as an array, resulting in an array of arrays. The array_count function can then be used to count arrays that meet the conditions (or use of array_filter and array_length as above):

array_count( relation_aggregate( 'Projects', 'array_agg', array("technology", "authorised") ), array('solar', 'TRUE') )
Improve this answer
7
  • Amazing! Works great, thanks! I had to update to 3.28 as the array_count function is not available in earlier software releases. Commented Mar 16, 2023 at 11:53
  • Good to know, thanks! I hadn't realised it was a recent addition. I will add the QGIS version to my answer. Commented Mar 16, 2023 at 11:55
  • Using array_length with array_filter should work for versions prior to the array_count function beig added. When I get time I will offer an alternative expression. Commented Mar 16, 2023 at 11:59
  • Is there a similar array_count function that will count projects that meet more than 1 condition? i.e. number of projects where "technology" = solar AND "authorized" = TRUE? Commented Mar 16, 2023 at 12:08
  • 1
    Superstar! Thanks 🙏🏻 Commented Mar 16, 2023 at 12:59

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.