Re: [Qgis-user] using nested case statements in labelling

[damos]

Sorry that was Regis and Delaz

On 22 Dec 2016 17:19, "damien stephens" <damien.steph...@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+s1560n5300800...@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=5300800=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=5300800=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=5300800=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=5300779=ZGFtaWVuLnN0ZXBoZW5zQGdtYWlsLmNvbXw1MzAwNzc5fC00MTgwMzg3MzU=>
>> .
>> NAML
>> <http://osgeo-org.1560.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer=instant_html%21nabble%3Aemail.naml=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace=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.___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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+s1560n5300800...@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=5300800=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=5300800=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=5300800=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=5300779=ZGFtaWVuLnN0ZXBoZW5zQGdtYWlsLmNvbXw1MzAwNzc5fC00MTgwMzg3MzU=>
> .
> NAML
> <http://osgeo-org.1560.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer=instant_html%21nabble%3Aemail.naml=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace=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.___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

[Qgis-user] using nested case statements in labelling

[damos]

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.nabble.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
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

[Qgis-user] Mssql offline table with sync

[damos]

Hi. was wondering if there is any currently available method to have a
similar functionality to the way that mapinfo works with MSSql tables.
in a nutshell you have the option of a linked table or a live table.
I would like the linked option, where once the connection is made a local
copy of the table is created. this local copy can then be refreshed with the
push of a button.
This is great for field work where you dont have a reliable internet
connection. I know that I can get a live connection, save the live table as
a layer definition file then save as a shape or spatiallite table, then copy
and paste the styles across or set the default qml file, then close and
reopen the LDF and resave but that is a lot of messing around.
any assistance gratefully accepted.

thanks
Damien



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Mssql-offline-table-with-sync-tp5278634.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

[Qgis-user] avl2qml.py script for onverting argis legend to qgis qml not working

[damos]

Hi all.
Has anyone attempted to use the avl2qml script  avl2qml home page
  ?
it is supposed to be able to convert arview 3 legends (avl) to qgis styles
(qml). I have tried it but get erors, which as a new user with python, I
really cant understand.
would love some help on this or another way to covnert AVL to QML.
I have access to Mapinfo and arcgis if that helps.
cheers




--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/avl2qml-py-script-for-onverting-argis-legend-to-qgis-qml-not-working-tp5266719.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user