[Qgis-user] using nested case statements in labelling

[damos]

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-
> statements-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-tp5300779p5301027.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/af7cf44d/attachment.html>