Skip to main content
Geographic Information Systems

Return to Answer

added images
Source Link
she_weeds
she_weeds
  • 13k
  • 1
  • 32
  • 64

Example dataset with NULL value highlighted:

enter image description here

Result:

enter image description here

Example dataset with NULL value highlighted:

enter image description here

Result:

enter image description here

expanded explanation
Source Link
she_weeds
she_weeds
  • 13k
  • 1
  • 32
  • 64

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 streamlinedelegant approach that handles NULLs better, consider array functions:

You won't even need to wrap onThis converts the relevant attribute values, with appropriate prefix/suffix, — e.g. *Narrabeen Group as, \n'Ref:'|| Rnbh is a linebreak, and anyBald 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 value init 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 fieldstring (regardless of whether it's concatenated withi.e., text before or) using array_to_string() with your specified delimiter to use after iteach element of the - becauseresulting array (remember, this is already filtered to remove NULL concatenated with anything is stills so there won't be unnecessary delimiters after a NULL value) will simply.

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

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!)

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.

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

You won't even need to wrap on * as \n is a linebreak, and any NULL value in a field (regardless of whether it's concatenated with text before or after it - because NULL concatenated with anything is still NULL) will simply not appear in the label. You can easily add more fields or change the delimiter as needed without worrying about whether you need an additional space.

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:

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!)

Source Link
she_weeds
she_weeds
  • 13k
  • 1
  • 32
  • 64

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.

For a more streamlined 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'
)

You won't even need to wrap on * as \n is a linebreak, and any NULL value in a field (regardless of whether it's concatenated with text before or after it - because NULL concatenated with anything is still NULL) will simply not appear in the label. You can easily add more fields or change the delimiter as needed without worrying about whether you need an additional space.