First possibility: use concat and replace double commas where you have empty entries:
replace( concat (field_a,', ',field_b,', ',field_c), ', ,', ',')
Second possibility: use arrays to concatenate, they concatenate NULL
values as empty strings that you can then remove. Use one of this expressions:
replace( array_to_string (array (field_a,field_b,field_c)),',,',',')
Or, alternatively:
array_to_string (array_remove_all (array (field_a,field_b,field_c),''))
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.
Best Answer
It seem that java and PHP (and maybe other langage as well…) revert to 0 for null integer value as the default behavior in array. QGIS likely do the same.
To bypass this default behavior you have to explicitly define how to represent null value. you may for exemple replace
array("field1","field2")
by
array(if( "field1" IS NULL, NULL,"field1" ),if( "field2" IS NULL, NULL,"field2"))