<!DOCTYPE html><html><head><meta http-equiv="Content-Type" content="text/html; charset=us-ascii"><meta name="Generator" content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<style type="text/css">body { font-family:'DejaVu Sans Mono'; font-size:12px}</style>
</head>
<body lang="EN-AU" link="blue" vlink="purple">Am 20.09.2015, 09:54 Uhr, schrieb Phil (The Geek) Wyatt <phil@wyatt-family.com>:<br><br><blockquote style="margin: 0 0 0.80ex; border-left: #0000FF 2px solid; padding-left: 1ex"><div class="WordSection1"><p class="MsoNormal">Hi Folks,</p><p class="MsoNormal"><o:p> </o:p></p><p class="MsoNormal">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.</p><p class="MsoNormal"><o:p> </o:p></p><p class="MsoNormal">Can someone give me a quick heads up on how to NOT add the data from a field that has NULL?</p><p class="MsoNormal"><o:p> </o:p></p><p class="MsoNormal">Chances are I will be doing this regularly so I am keen to document the required expressions for others to use as well.</p><p class="MsoNormal"><o:p> </o:p></p><p class="MsoNormal"><span style="mso-fareast-language:EN-AU"><o:p> </o:p></span></p><p class="MsoNormal"><span style="mso-fareast-language:EN-AU">Cheers - Phil</span></p><p class="MsoNormal"><span style="mso-fareast-language:EN-AU"><o:p> </o:p></span></p><p class="MsoNormal"><span style="mso-fareast-language:EN-AU">Volunteer Mapper - <a href="http://www.redcross.org.au/volunteering.aspx"><span style="color:windowtext;text-decoration:none">Red Cross</span></a></span></p><p class="MsoNormal"><o:p> </o:p></p></div></blockquote><div><br></div><div>Hi,</div><div>having NULL values in attributes is not easy to handle for those who have no idea what terms to search for on the internet.</div><div><br></div><div>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).</div><div><br></div><div>Concatenating coalesce("Fieldname1",'') || coalesce("Fieldname2",'') ...</div><div><br></div><div>But this will just paste together all attributes, so add || ' ' to the field name to get spaces between the attributes.</div><div><br></div><div>coalesce("Fieldname1" || ' ','') || coalesce("Fieldname2" || ' ','') ... (it seems that this handles NULL & something = NULL)</div><div><br></div><div>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.</div><div>in cell B1 start ="coalesce(" & A1 & " ||'','') ||</div><div>and drag this down. </div><div>You can than just copy the range it paste it into the expression editor in QGIS. Remove the last ||</div><div><br></div><div>Hope this helps.</div><div><br></div><div>Bernd</div><div><br></div><div><br></div><br><br><br><div id="M2Signature"><div>-- </div><div>Bernd Vogelgesang<br>Siedlerstraße 2<br>91083 Baiersdorf/Igelsdorf<br>Tel: 09133-825374</div></div></body></html>