[Qgis-user] Concatenate address fields with NULLS

[Bernd Vogelgesang]

Am 20.09.2015, 09:54 Uhr, schrieb Phil (The Geek) Wyatt  
<phil at wyatt-family.com>:

>
> Hi Folks,
>
>
> I am working with LIST Address Points data from  
> http://listdata.thelist.tas.gov.au/opendata/ >(Specifically Clarence  
> Municipality) and I need to concatenate into one field the full address  
> of >each location. I am struggling to figure out how to do it when there  
> are fields for unit numbers, >building names, numbers to and from etc.  
> Many of the fields are also NULL so clearly I want those >fields  
> disregarded.
>
>
> Can someone give me a quick heads up on how to NOT add the data from a  
> field that has NULL?
>
>
> Chances are I will be doing this regularly so I am keen to document the  
> required expressions for >others to use as well.
>
>
>
> Cheers - Phil
>
>
> Volunteer Mapper - Red Cross
>
>

Hi,
having NULL values in attributes is not easy to handle for those who have  
no idea what terms to search for on the internet.

There is the coalesce function in field calculator:  
coalesce("Fieldname1",'') will return your attribute, in case its NULL it  
will return en empty string (or what ever you like).

Concatenating coalesce("Fieldname1",'') || coalesce("Fieldname2",'') ...

But this will just paste together all attributes, so add || ' ' to the  
field name to get spaces between the attributes.

coalesce("Fieldname1" || ' ','') || coalesce("Fieldname2" || ' ','') ...  
(it seems that this handles NULL & something = NULL)

For a lot of fields, this is a boring work to put it into the expression  
editor , so I normally paste the attribute table (click the up-left margin  
cell to mark everything, then hit ctrl-c) into a spreadsheed. Delete all  
data except the header line, clip and insert this line transposed, so it  
starts in cell A1 downwards.
in cell B1 start ="coalesce(" & A1 & " ||'','') ||
and drag this down.
You can than just copy the range it paste it into the expression editor in  
QGIS. Remove the last ||

Hope this helps.

Bernd





-- 
Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150920/d84feabf/attachment.html>