Concatenating labels in QGIS

Question

I am trying to display labels for polygons in a dataset. Each polygon has about 5 different fields that I want to display. Depending on the polygon, there may be NULL values in the field(s) that I am trying to display in the label. I understand that Concatenate is designed to handle NULL values. The following is the string I am using to display the labels in the expression string dialogue:

CONCATENATE("GROUP" || '**Ref: ' || "Lett_Sym_1"  || ' **' || "Formation" || '**' || "Period"  || ' age**' || "LITHOLOGY")

Noting that I am wrapping on the character '*', I have also tried CONCAT instead of CONCATENATE, which gave me no results. What this produces is dozens of labels regardless of the polygons actually being present in the extent, instead of one label per polygon. I can get around this by filling NULL values with a value such as 'Unknown' however I would like to understand how to fix the issue.

Answer 1

This is because concatenate() is an aggregate function (i.e. it looks up the values for that attribute across all features), so when you try for example concatenate("GROUP") you'll still end up with a whole lot of results (the GROUP value for every single feature) joined together - e.g., Wianamatta GroupWiannamatta GroupWiannamatta GroupNarrabeen GroupNarrabeen Group and so on, for a single label.

When you use concatenate() with multiple fields then all those fields will, again, be represented for every single feature in your dataset, even though the label is only for one feature. So in your use case this is simply not the appropriate function, even though concatenate() does let you specify delimiters which is handy.

concat() is the function you are after but it takes each field as a parameter, rather than you having to concatenate it yourself using pipes (||). However, you still have to manually insert each delimiter and the appropriate space after every single field reference, which is quite tedious.

For a more elegant approach that handles NULLs better, consider array functions:

array_to_string(
    array_filter(
        array("GROUP", 
            'Ref: '||"Lett_Sym_1",
            "Formation", 
            "Period"|| ' age',
            "LITHOLOGY"),
        @element IS NOT NULL),
    '\n\n'
)

This converts the relevant attribute values, with appropriate prefix/suffix, — e.g. Narrabeen Group, 'Ref:'|| Rnbh, Bald Hill Claystone, Triassic||' age', Dominantly red shale and fine to medium sandstone — into an array (['Narrabeen Group', 'Ref: Rnbh', 'Bald Hill Claystone', 'Triassic age', 'Dominantly red shale and fine to medium sandstone']).

This array can then be filtered using array_filter() so that if for example "LITHOLOGY" is NULL it will remove it from the array. Or if "Lett_Sym_1" is NULL then 'Ref: '||"Lett_Sym_1" will also be NULL and that entire element will be filtered out.

The resulting array can then be converted to a string (i.e., text) using array_to_string() with your specified delimiter to use after each element of the resulting array (remember, this is already filtered to remove NULLs so there won't be unnecessary delimiters after a NULL value).

As \n is a linebreak, the above expression does not even require you to wrap on the * character.

The benefit of this method is that you can easily add more fields or change the delimiter as needed without the tedium of editing a long concat() expression, or worrying about whether you need an additional space, or if you've got a text prefix like Ref: hanging out on its own without a corresponding non-null value... (in which case, don't replace all the pipes with commas!)

---

Example dataset with NULL value highlighted:

Result: