I want to simplify the variable ("SIOSE_CODE") which has values like this:


The first number (here 90 resp. 65, resp. 70) is a percentage of the land-use class which is most frequently at this location.

Now I want to extract the land-use class (MTRfr, PST or FDCfr), but only if the percentage is higher than 60%.

I tried it with this code:

First I created a new variable "Percentage" and filled it with the output of:

WHEN (substr("SIOSE_CODE", 1, 1) = 'A' )
OR (substr("SIOSE_CODE", 1, 1) = 'I')
OR (substr("SIOSE_CODE", 1, 1) = 'R')
THEN (substr("SIOSE_CODE", 3, 2))

Herethen I want to extract the land-use class:

WHEN ("Percentage" > 55)
THEN (substr("SIOSE_CODE", 5, 100))
ELSE (regexp_replace("SIOSE_CODE", '[(](.*)?[)]', ''))

But the output of my new variable is NULL.

The output of the variable "Percentage" seems to be okay.

Does somebody find the mistake?

  • Both of your syntax worked for me. I could get NULL only when I set the output field type to number (integer or real). Could you double-check? – Kazuhito Feb 5 '19 at 12:12
  • What type does your "Percentage" attribute inherit? Integer or float? – Taras Feb 5 '19 at 12:43
  • As @Kazuhito already clarified, I do not see any issues in "If-statement"as well. – Taras Feb 5 '19 at 12:45
  • I think I would just change this tiny part THEN (substr("SIOSE_CODE", 5, 100)) into THEN right("SIOSE_CODE", length("SIOSE_CODE")-4). – Taras Feb 5 '19 at 12:53

I've just used your code and It works perfectly. I think you need to be careful at selection of the data type.

  • First piece of code: select integer
  • Second piece of code: select string

enter image description here enter image description here enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.