Use field calculator QGIS: if value in one column, then value in new column

You can use regexp_match function to find starting position of the word within the string. Than with using condition if you can test if there is string occurence (e.g. starting position > 0) and set the value for TRUE (your value) and FALSE (0).

It should look like this:

if(regexp_match(taxa, 'Calluna_vulgaris')>0,1,0) +
if(regexp_match(taxa, 'Porpidia_crustulata')>0,2,0) +
if(regexp_match(taxa, 'Rhizocarpon reductum')>0,2,0) +
if(regexp_match(taxa, 'Micarea_erratica')>0,2,0)

Maybe there is better regexp solution, but I'm not so regexp ninja.

I think the most robust method for you will involve having your species-value list on another layer as a CSV or similar - in other words, a lookup table. Trying to match values in expressions will be incredibly inefficient, difficult to edit, and prone to error.

It should be easy to create such a table in any spreadsheet program, even a basic text editor, then import it into QGIS using Add Delimited Text Layer. (Make sure to trim fields so that no extra spaces make their way in)

Once you have that you could use the following expression (built on @csk's excellent suggestion to use arrays) to return the values required and sum them together. If one or more species cannot be found in the lookup table it will return a NULL which should prompt the user to check the data.

---

This is the lookup table I have set up called taxa_csv in the layers list (note the name).

This is the expression I used in field calculator (note lookup table name is in expression).

eval(replace(array_to_string(array_foreach(string_to_array("taxa",'#'),
to_real(attribute(get_feature('taxa_csv','taxon',@element),'value'))),',','NULL'),',','+'))

Result - note NULLs for entries where species not in lookup table.

---

Some notes:

• The lookup table name (taxa_csv) and corresponding column names (taxon, value) in the expression are case-sensitive. If you rename the lookup table and/or column names, just remember to change the names accordingly in this expression.
• Even if your source values are all text (e.g. CSV) the expression will convert it to a numeric format (to_real()). (However, 1/3 won't work - I suggest converting this to a decimal number)
• If you want to treat species not found as 0 rather than NULL replace accordingly in the expression. I don't recommend this though as it can be misleading
• Does anyone know a good way to sum everything in an array in QGIS Field Calc? Had to kludge it together by converting back to a string and replacing commas with plus signs then evaluating it with eval()

Another way would be to use the replace function repeatedly to replace the species names with their corresponding values, replace the # signs with + signs, then use eval to calculate the sum. Eg,

eval(
 replace(
  replace(
   replace(
    replace(
     replace(
      replace(
       replace("Taxa",'Baeomyces_rufus', 1),
      'Calluna_vulgaris',1),
     'Porpidia_crustulata',2),
    'Rhizocarpon reductum',2),
   'Micarea_erratica', 2),
  'Hypnum_jutlandicum', 0.33333),
 '#','+'))

It's pretty cumbersome, though.

---

I suspect there's a possible clever solution using arrays, but I haven't quite worked it out. Here's what I have so far:

• Use string_to_array("Taxa", '#') to convert the "Taxa" field to an array

• Create an array with all the possible species names, and create another array with their corresponding values in the same order. Using the example you gave, those arrays would look like this:

  • All_Taxa : ('Calluna_vulgaris', 'Baeomyces_rufus', 'Porpidia_crustulata', 'Rhizocarpon_reductum', 'Micarea_erratica', 'Hypnum_jutlandicum')

  • All_Values: (1,1,2,2,2,0.3333)

• Use array_find on every value from the "Taxa" array, to find its position within the All_Taxa array. Then use array_get to retrieve the corresponding values from the All_Values array.

  • The array_filter function might be useful - you could filter the All_Values array based on an expression that achieves the above.