QGIS Field Calculator – How to Sum Values in Rows with Exceptions

attribute-tableexpressionfield-calculatorqgissummarizing

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.

Best Answer

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.

Related Question