6

In QGIS 3.18. In a shapefile attribute table, I need to create a field that concatenates several other fields with a comma delimiter. Some of those fields are NULL.

Currently my approach has been what I thought was the foolproof "Field1" || ', ' || "Field2" || ', ' || "Field3". If all 3 of these fields contain a non-NULL value, there is no problem (example: Field1=A, Field2=B, Field3=C, result=A, B, C)

However, if one or more fields are NULL, the result is also NULL. (example: Field1=A, Field2=NULL, Field3=C, result=NULL).

I suspect there is a way to account for NULL values in the field calculator without resorting to Python. I do not believe that concat or coalesce can help me here, but please correct me if I'm wrong.

Note: I'm not concerned with labels here, just attributes.

4
  1. First possibility: use concat and replace double commas where you have empty entries:

    replace( concat (field_a,', ',field_b,', ',field_c), ', ,', ',')

  2. 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),''))

enter image description here

1
  • It seems array expressions are the answer to nearly all of my questions :) Soon I will be an array expert. Thanks @Babel.
    – pete
    Sep 19 at 11:55
1

Another possibility in case you want to concatenate all fields in your attribute table: map_akeys( attributes( )) gets the names of all attributes, array_to_string with delimiter + gets a string like a+b+c that you can than evaluate with eval() so that the actual contents of a, b and c are added:

eval (array_to_string (map_akeys( attributes( )), delimiter:='+'))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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