5

I have a field with singular abbreviations ("BB", "BC", and "BG") that corresponds to a table with full names (catfish, rainbow trout). It ranges from 1-10 abbreviations delimited by commas.

This is an example of the data.

enter image description here

What statement or process could I use to take these abbreviations and get them into one field with the full names expressed minus all the extra commas?

In the picture below, the column "APPENDED" has the abbreviations and the "Species" is what I want to fill in with the first one manually completed.

enter image description here

5
  • 2
    Not clear. Please add example of resust you need.
    – RainForest
    Mar 29, 2023 at 6:05
  • 1
    Like a comma separated text? Something, SomethingElse, blabla
    – BERA
    Mar 29, 2023 at 6:08
  • 1
    BTW QGIS expression for string concatenation of attributes a, b and c: concat("a","b","c") or "a" || "b" || "c"
    – RainForest
    Mar 29, 2023 at 6:11
  • 1
    OP, I hope you manage to edit the question so that it can be opened again, and that you can mark any answer as the solution if they have answered your query - otherwise the question and answer get buried and won't be as easily referenced by people with the same issue.
    – she_weeds
    Mar 30, 2023 at 23:06
  • Your updated edit should also include what reference you are using to match up each abbreviation to the full name (e.g. BK = brook trout, RB = rainbow trout). Is there a lookup table (e.g. excel file or CSV) in your project? That would be the easiest way of doing it.
    – she_weeds
    Apr 4, 2023 at 4:34

2 Answers 2

9

I would recommend using arrays to concatenate your data, especially as you will have some blank/null results which will make typical concatenate (||) not work well and you would need to wrap each field around with a coalesce() which is time-consuming.

You mention full names without any additional info - I will assume you have a separate table in your QGIS project that acts as a lookup table, something like below as an example.

abbr fullname
BB Cutfish
BC Rainbow Trout
BG Mahi-mahi

Let's call this lookup layer LookupTable and the layer in your screenshot above as FishObs.

Assume the names of all the fields you want to concatenate start with FISH.

  1. Select the FishObs layer in your list of layers and open Field Calculator.
  2. Select an appropriate text field you want to enter the concatenated value into (Update existing field), or create a new field of text type and sufficient length.
  3. Use this expression:
array_to_string(
    array_filter(
        array_foreach(
         array_filter(map_akeys(attributes()),
                      @element ilike 'FISH%'),
        attribute(get_feature('LookupTable','abbr',eval(@element)),
       'fullname')),
    @element is not null),
', ')

Example result:

enter image description here

The full names are in the order of the fields as they appear, but you can further order the array using array_sort() in between array_to_string() and array_filter() above.


Step by step explanation:

  1. Generate list of field names as an array using map_akeys(attributes()). I believe by default this array is alphabetically sorted.
  2. Filter field name array to only those starting with FISH (array_filter(), condition @element ilike 'FISH%')
  3. Evaluate each field name in the array from Step 2, using array_foreach() and eval(@element). eval() takes the field name and treats it like a field reference, turning e.g., 'FISH1' from a text string in the array of field names, to "FISH1" a field reference that yields a value for each feature. — This will result in an array like ['BC','BN','RB','','','',''], with '' appearing where evaluating a field returns an empty value (e.g. see FISH4 to FISH7 in features 12-14 in screenshot).
  4. Lookup the full name while iterating over each evaluated value in the array from step 3, using LookupTable and the functions get_feature(). First this matches eval(@element) (e.g. 'BC') to the record in LookupTable with a matching attr value, and then attribute(), returns the corresponding fullname value for that record (i.e., BC returning Rainbow Trout). Now your array will look like ['Rainbow Trout','Swordfish','Mackerel',NULL,NULL,NULL,NULL] because looking up an empty value returns NULL.
  5. Exclude NULL values from the arrays in step 4 using array_filter() and @element IS NOT NULL. This means you now get an array like ['Rainbow Trout','Swordfish','Mackerel'] for features 12-14, ['Cutfish','Yellowfin Tuna'] for feature 2, etc. This solves the issue of blank/NULL values that plague most concatenate solutions.
  6. Concatenate the array into a string using array_to_string() and your preferred concatenator (here, ', '). Simply turns the array (shown with square brackets ['Cutfish', 'Yellowfin Tuna']) to a string ('Cutfish, Yellowfin Tuna'). This is required so that it can actually be depicted in your dataset.

Customisations:

  • Re-order the concatenated fields - at step 2, use array_sort() at this stage to order the fields, or array_prioritize() if you wish to manually specify an order that is not alphabetical.
  • Capitalisation, extra text etc - at step 4, use lower(), ||'text' etc around the last parameter of array_foreach().
  • Add text in front of the concatenated array - simply add 'Your text here '|| to the front of the whole expression. ||' Your text here' at the back of the whole expression for text at the back.
  • Return some other value where there are no resulting values at all, e.g. feature 1 in screenshot where result shows as NULL - use coalesce() around entire expression. E.g. coalesce(expression, 'No records found') so that 'No records found' is returned in case of NULL.
  • Change concatenator e.g. from comma to semicolon, no space, - change accordingly in step 6
3

You might want to use the Field Calculator and tick the option "Create a new field", as in the following picture:

enter image description here

  • One method could be simply using concat("FISH2", "FISH3").
  • A second method, and my preferred one, would be simply using ||. For example, using the expression "FISH2" || "FISH3". In case you want to add a blank space between abbreviations, you should add it as follows: "FISH2" || ' ' || "FISH3"

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.