6

I have given my objects multiple value that represent a "need for action". I want to sum up those individual numbers to an overall "pressure for action".

I have multiple objects with values in ~10 fields.
Sometimes, fields do not have a value (NULL). Sometimes I have a 99 as an error value.

If I just sum up my fields by "field1" + "field2" + "field3" + ..., I end up with NULL as result, if there was a NULL in any value. So I need to exclude NULL values from my expression. Also, I want to exclude my 99 values.

Sample data:

id field1 field2 field3 field4 expected result
1 5 NULL 2 17 24
2 2 1 1 9 13
3 0 2 99 12 14
4 NULL 1 99 19 20

I am looking for either an expression I can use or if only possible with Python, a code that I can just copy/paste and edit my column names (I do not know Python, so I would need a proper explanation of the code).
I imagine a code that first checks the value of a field. If the value is NULL or 99, it skips to the next field. All other values get added up.

I found this question : Calculating field in which null values may be present?, which describes a somewhat same problem, but is focused on ArcGIS. I also have no clue what the given code there says and how to adapt it to my problem.

I can not change the numbers in my value fields as they are important on their own.

I am using QGIS 3.24 on Windows 10.

4
  • 1
    Are you asking how the sum expression works with a filter?
    – Erik
    Mar 4, 2022 at 8:25
  • I don't know - do I?
    – e-shirt
    Mar 4, 2022 at 8:32
  • 1
    why don't you just reassign the null and 99 values as zero(0) which has no effect on sum operation?
    – RS_girl
    Mar 8, 2022 at 12:45
  • @RS_girl as I stated I need those values to stay as there is a different meaning wether the object has 0, 99 or NULL as value and I will continue to work with the other columns on their own as well
    – e-shirt
    Mar 9, 2022 at 8:15

1 Answer 1

7
+50

Case 1. Sum of all fields, except NULL and 99 values

array_sum(array_filter(map_avals(attributes($currentfeature)), @element not in (99)))

It sums up all fields of a feature except for values 99 or NULL. You can add more values to exclude to the list not in (99) if you wish. There is no need to exclude NULL, as array_filter() will automatically ignore these.

Note: This will also add the "id" field to the sum. To exclude it, see solution 2 or 3


Case 2. Sum of specific fields, except NULL and 99 values

In case you do not want to sum all fields, but only some specific, you can use a slightly different expression:

array_sum(array_filter(array("a","b","c"), @element not in (99)))

Specify the field names within the array. Here array("a","b","c").


Case 3. Sum of all fields, except one fixed specified field (e.g. "id") and except NULL and 99 values

In case you want to sum up all fields, except for one and not list them by hand, you can do it like this:

array_sum(array_filter(map_avals(map_delete(attributes($currentfeature), 'id')), @element not in (99)))

This deletes the "id" field from the sum, but takes all other fields into account.


Explanation on the expression

attributes($currentfeature) will return a dictionary (a map) of all fieldnames and values of the current feature. To turn this into an array of only fieldvalues we can use map_avals(). Before doing that, we can filter out single fields by using map_delete() if wanted.

Similar to attributes() does array("field1", "field2"). The difference here is that it will not return a dictionary of field name and value, but only the values as array, therefore here map_avals() is not needed.

In both cases we now have an array of the desired field values. To filter out NULL and other specific values we can use array_filter(). This expression iterates over the array and during the iteration we can access the current value by using @element. By doing a comparison (not in (x) or <> x) we can filter the values to keep. array_sum() finally sums up all values within the remaining array.

To fully understand complex expressions, I can suggest to split it into parts and just try out what the result of each part is. Additionally you can read up in the QGIS Docs | List of functions.

4
  • 3
    I was just up to put the answer @_@ : array_sum(array_filter(array("field1", "field2", "field3"),@element != 99))
    – Taras
    Mar 8, 2022 at 12:52
  • 2
    Be careful map_avals(attributes($currentfeature)) will also consider the "id" field
    – Taras
    Mar 8, 2022 at 12:55
  • Thanks! Option B is exactly what i was looking for as there are many more other fields in my real table that should not be considered. Definitely either the expression explanations or just my translation could be improved for beginners, I think. I keep comin here for the most simple questions, just because I don't understand the expressions and what they do.
    – e-shirt
    Mar 9, 2022 at 7:38
  • Thanks, that explanation really helped! afaik the list in the docs only shows the same texts you get, when clicking on the expressions in field calc. As a GIS advanced user or if you're keen on programming in general, that may suffice, but as a GIS beginner with no knowledge of programming I tend to find my limit in understanding what I have to input as filter, exception, value, etc.
    – e-shirt
    Mar 9, 2022 at 8:25

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.