3

In my shapefile, I have calculated some indicators for each polygon. Now I want to create a new field having sum of these indicators.

I created new field and populated it with desired values using expression in QGIS. But, the resulting column have some blank rows despite not all of the indicators being NULL. it is observed only for those rows, where any of the indicator is NULL. I have used this expression

enter image description here

and here are all the values:

enter image description here

enter image description here

enter image description here

How to solve this?

Improve this question
5
  • Which field types do your other columns have?
    – Erik
    Jul 15, 2021 at 10:57
  • All the fields are of type qlonglong and type name is Integer64
    – rgt
    Jul 15, 2021 at 10:59
  • Try "crabs" + "schrimps" instead of crabs + schrimps
    – Taras
    Jul 15, 2021 at 11:06
  • "crabs" + "schrimps" instead of crabs + schrimps didn't work. Still getting NULL And nullif is also not working
    – rgt
    Jul 15, 2021 at 11:32
  • 2
    @Taras actually nullif() does the oposite: if value1 equals value2, then the function returns Null, else it returns value1. In this case, if crabs==0 then the function returns Null, else it return the value of crab... which remains null
    – JGH
    Jul 15, 2021 at 13:05

3 Answers 3

Reset to default
6

The function to replace a null by a value is coalesce()

coalesce(crayfish,0) + coalesce(shrimp,0) + ...
Improve this answer
1
  • Thank you for your comment :-)
    – Taras
    Jul 15, 2021 at 13:13
5

Use the following expression the get the sum of all fields in your attribute table and where NULL is treated as 0 (needs QGIS 3.18+):

array_sum (map_avals (attributes ( ) ))

If you do not want all fields to sum up, but exclude some of the attributes, use this expression and add a comma-delimited enumeration of the attribute-names you want to exclude (here: fid, value):

array_sum (array_filter (map_avals (attributes( ) ), @element not in (fid, value)))

Screenshot, creating the sum of all attribute values except the last two ones (fid and value), where NULL is ignored: enter image description here

Improve this answer
3

Replace all NULL values with "0" - Open field calculator, set "Update Existing Field" and put in the expression (you need to repeat this for all columns):

if("crayfish" is null, 0, "crayfish")

Improve this answer
3
  • This worked. But I have to do it for each indicator/ columns. I exported the attribute table in excel file and then created the desired column and created a new field in attribute table in QGIS. Using Join, I added the field to attribute table.
    – rgt
    Jul 15, 2021 at 12:02
  • huh, this seems quite complicated. why did you export it to excel and then re-import it to QGIS?
    – sn1ks
    Jul 15, 2021 at 12:08
  • 1
    Because in excel, all the 'NULL' values are treated as blank cell (0), so, now, I will get the correct sum. This process is a bit complicated, but since I need to create only 1 or 2 column which has sum, I did this. In QGIS, I had to convert each column's NULL to 0.
    – rgt
    Jul 15, 2021 at 13:08

Your Answer

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

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