QGIS Attribute Table – Concatenate Multiple Fields with Null Values

attribute-tableconcatenationnullqgis

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.

Best Answer

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

Solution: basic principle

You can use QGIS expressions with aggregate() function (see documentation): it's very use case is searching across multiple rows/features. Additionally, it is able to refer to any other layer, not just the current one where you apply the expression, thus making "joining" attributes possible.

To get a dynamic output, choose a virtual field: the content will then be updated if the entries change in the connected layer.

Detailed explanation of how to fill in the arguments

Get the layer you want to get the information from (line 5), here publications
Define the kind of aggregation (line 6), here concatenate
Set the fieldname of the connected layer (line 7), here publication
Filter condition (line 8): only consider the entry if the field in the connected layer (here: fieldname site) matches the content in the current layer. This last step is a bit tricky, as inside the aggregate function, when referring to a fieldname, it only takes fieldnames of the layer defined on line 5.
For that reason, first create a variable (lines 1-3). The variable here is called site and contains the value of the field site_name of the current (sites table) fieldname. You can then refer to this variable using @site inside the aggregate function (line 8).

The expression to use

with_variable(
    'site',
    site_name,
    aggregate( 
        'publications',
        'concatenate',
        publication, 
        filter:= regexp_match(site, @site), 
        concatenator:=','
    )
)

Best Answer

Related Solutions

FME – Concatenate Attributes Ignoring NULL/Missing Values

QGIS – Expression-Based Table Relation without Joins and Relates

Solution: basic principle

Detailed explanation of how to fill in the arguments

The expression to use

Related Question