[Qgis-user] using nested case statements in labelling

[damos]

Sorry that was Regis and Delaz

On 22 Dec 2016 17:19, "damien stephens" <damien.stephens at gmail.com> wrote:

> Thanks very much Regis and Relaz.
> Much appreciated.
>
> On 21 Dec 2016 16:08, "Régis Haubourg [via OSGeo.org]" <
> ml-node+s1560n5300800h55 at n6.nabble.com> wrote:
>
>> Hi,
>> you are simply facing the SQL standard behavior of the | operator when it
>> compares something to a NULL value, the whole result will be NULL.
>> In SQL, you can avoid that by wrapping each value in a "coalesce(myvalue,
>> '')" that will replace NULL by an empty string.
>>
>> In QGIS, you can also use the "+" operator, and this one does not sends a
>> NULL like the pipe operator. Far more simple indeed, but less portable to
>> pure SQL .
>> Cheers
>> Régis
>>
>>
>> 2016-12-21 3:33 GMT+01:00 damos <[hidden email]
>> <http:///user/SendEmail.jtp?type=node&node=5300800&i=0>>:
>>
>>> HI I am trying to do a nested case statement for a labelling task.
>>> I have a list of mines of varying sizes with different commodities and
>>> would
>>> like to label with the name of the mine and the amount of each commodity
>>> at
>>> the mine in a new line below the name. I only want to label the mines
>>> that I
>>> have decided are signifcant and for this I have made a new column in my
>>> table called significant.
>>> My difficulty is that not all mines have the same commodities, for
>>> example
>>> one mine has copper (Cu) and no lead(Pb), while another has both. yet
>>> another will only have Pb.
>>> i can easily label all of the sigificant mines and their copper amoutns
>>> with
>>> a suffix of kt Cu using this code:
>>>
>>> case when  "signifcant"  = 'yes' then  title( "Name" ) ||   '\n'   ||
>>>  case
>>> when "Cu_e_t" <1 then '' else round("Cu_e_t"/1000,0) || 'Kt Cu' end else
>>> null end
>>>
>>> however if I then try to add in the Pb amounts like this;
>>> case when  "signifcant"  = 'yes' then  title( "Name" ) ||   '\n'   ||
>>>  case
>>> when "Cu_e_t" <1 then '' else round("Cu_e_t"/1000,0) || 'Kt Cu' end ||
>>> case when "Pb_e_t" <1 then '' else round("Pb_e_t"/1000,0) || 'Kt Pb' end
>>> else null end
>>>
>>> I lose all of the Cu only mines and only get labels for those with Cu
>>> and Pb
>>> or just Pb.
>>> I understand that I am doing something wrong with the nested case
>>> statements, but cant figure out what.
>>>
>>> any help would be much appreciated.
>>> int the mean time I am going back to do the formula in excel where I am
>>> capable.
>>>
>>>
>>>
>>>
>>> --
>>> View this message in context: http://osgeo-org.1560.x6.nabbl
>>> e.com/using-nested-case-statements-in-labelling-tp5300779.html
>>> Sent from the Quantum GIS - User mailing list archive at Nabble.com.
>>> _______________________________________________
>>> Qgis-user mailing list
>>> [hidden email] <http:///user/SendEmail.jtp?type=node&node=5300800&i=1>
>>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> [hidden email] <http:///user/SendEmail.jtp?type=node&node=5300800&i=2>
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> ----------------------------------
>> Régis Haubourg
>> GIS administrator and project manager
>> Administrateur de données géographiques et chef de projet SIG
>> Agence de l'eau Adour-Garonne
>>
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the discussion
>> below:
>> http://osgeo-org.1560.x6.nabble.com/using-nested-case-statem
>> ents-in-labelling-tp5300779p5300800.html
>> To unsubscribe from using nested case statements in labelling, click here
>> <http://osgeo-org.1560.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5300779&code=ZGFtaWVuLnN0ZXBoZW5zQGdtYWlsLmNvbXw1MzAwNzc5fC00MTgwMzg3MzU=>
>> .
>> NAML
>> <http://osgeo-org.1560.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>




--
View this message in context: http://osgeo-org.1560.x6.nabble.com/using-nested-case-statements-in-labelling-tp5300779p5301028.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20161222/e4b654c9/attachment.html>