5

I use the Field Calculator in QGIS to transform an attribute column with category names to numeric codes. I use the following expression:

CASE
    WHEN "category" = 'Foo' THEN 1
    WHEN "category" = 'Bar' THEN 2
    WHEN "category" = 'Boo' THEN 3
    WHEN "category" = 'Far' THEN 4
    /* etc. */
    ELSE 0
END

Is there an automatic approach using the field calculator for cases when there are lots of categories? I would expect an algorithm that iterates over all distinct values in the source field and sets the respective number in the target field in each row with automatic incrementation.

2
  • Is there a mapping for the categories from name to numeric value, or do you just want each new name to get the next available number? May 19, 2023 at 12:17
  • In this particular case there is no mapping and the next available number is fine.
    – winnewoerp
    May 19, 2023 at 17:06

2 Answers 2

5

If you have a long list of values and want to avoid adding each one manually, create an array of all unique values with concatenate_unique() and string_to_array(). For each field than check at which position (index) in the array it is listed with array_find(). The whole expression looks like this:

array_find(
    string_to_array( 
        concatenate_unique (category,concatenator:=',')
    ),
    category
)+1

This returns the category code in the order the values appear for the first time. As you have an array, it is easy to sort it in other ways, e.g. alphabetically, adding a array_sort() function that encloses the string_to_array() part.

Screenshot: the expression from above in action: enter image description here

1
  • Thank you. Successfully tested. Works perfectly.
    – winnewoerp
    May 19, 2023 at 17:26
4

Try the map() in combination with the map_get() and coalesce():

coalesce(map_get(map('Foo',1,'Bar',2,'Boo',3,'Far',4), "category"), 0)
1
  • Thank you. Successfully tested. This is much simpler than my basic solution using CASE WHEN ... THEN. However, it is still not dynamic, right? For many categories it would still by a lot of typing work.
    – winnewoerp
    May 19, 2023 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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