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.
Best Answer
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 exampleconcatenate("GROUP")
you'll still end up with a whole lot of results (theGROUP
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 thoughconcatenate()
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
NULL
s better, consider array functions: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"
isNULL
it will remove it from the array. Or if"Lett_Sym_1"
isNULL
then'Ref: '||"Lett_Sym_1"
will also beNULL
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 removeNULL
s so there won't be unnecessary delimiters after aNULL
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 likeRef:
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: