6

I am trying to fill out a field based on information in a different field in the same layer using a CASE Statement in the Field Calculator. My table structure looks like this:

SPECIES SHORT
Toad NULL
NULL B
Fish NULL
Plant NULL

My goal looks like this:

SPECIES SHORT
Toad T
NULL B
Fish F
Plant P

The Expression I am using and does not work looks like this:

CASE  
   WHEN "Species" LIKE 'Toad' THEN 'T'  
   WHEN "Species" LIKE 'Fish' THEN 'F'  
   WHEN "Species" LIKE 'Plant' THEN 'P'  
END

The result is that everything in "SHORT" ends up being NULL.

The Field "SPECIES" is a VALUE MAP widget type. "SHORT" is a TEXT EDIT. I am using QGIS 3.1

4
  • Edit to the Expression: It's "SPECIES" not "Species" just like in the table above – D.M. May 17 at 13:38
  • 4
    Try using a wildcard. Like WHEN "Species" LIKE '%Toad%' THEN 'T'. Otherwise, if you are looking for an exact match, dont use LIKE, but = instead, e.g. WHEN "Species" = 'Toad' THEN 'T' – MrXsquared May 17 at 13:42
  • I'm not sure if it was in 3.1 or if it came later, but since the field is a value map, wouldn't OP need to use represent_value("Species") = 'Toad' in the expression for it to work? – Gabriel May 17 at 13:50
  • As an aside, there is no inherent cost difference between using LIKE ... and regexp_match. If anything, the latter is slightly faster since LIKE is translated to a regex before evaluation. Source: reading the QGIS source code. (correct me if I'm misremembering this). – Alex Hajnal May 18 at 0:34
8

To get the same result you can use even a simpler formula with left() to get the first letter only.

left("SPECIES", 1) 
7

using wildcards % like '%Toad%' worked for me.

My Expression now looks like this:

CASE  
   WHEN "SPECIES" LIKE '%Toad%' THEN 'T'  
   WHEN "SPECIES" LIKE '%Fish%' THEN 'F'  
   WHEN "SPECIES" LIKE '%Plant%' THEN 'P'  
   ELSE "SHORT"
END  

I added the ELSE "SHORT" to prevent attributes to be lost if "SPECIES" is NULL

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.