Replacing element of array with data from another table using QGIS Expression

Question

How to do it in QGIS or QGIS Expression to replace the element of an array(cell value) of a table with a new value taken from another table (which contains the list of the element and its corresponding new value), see image below:

1

• Mayo: All the fields are of type string?
• Babel: By the way: as a general principle, I would always avoid spaces in Layer names (Table 2) and field names (Existing Data) - and any other names like filnames etc.

Answer 1

As long as the order of Old element and New Element are the same, you can use this expression on Table 1:

array_get(
 aggregate(
 'Table 2', -- the name of the layer with the field `New element`
 'array_agg',
 "uuid_trgt" -- the name of the field in `Table 2` containing `New element`
 ),
 array_find(
 aggregate(
 @layer, -- the current layer (`Table 1`)
 'array_agg', 
 "uuid_src" -- the name of the field in the current layer (`Table 1`) containing `Old element`
 ),
 "uuid_src" -- the name of the field in the current layer (`Table 1`) containing `Old element`
 )
)

enter image description here

The expression makes an array of all the values in the Old element field, then for each row of Table 1, finds the position in the array of the current value of Old element and retrieves the corresponding item from an array of New element values.

Answer 2

Use this expression to find the corresponding value, regardless of the attribute's order:

attribute (
 get_feature(
 'Table 2',
 'Old element',
 "Existing Data"
 ),
 'New Element'
)

Explanation:

1. Use function get_feature() to return the feature of Table 2 where the field Old element matches the attribute value of Existing Data in Table 1
2. then use attribute() to return the value of the attribute New Element of this feature.

By the way: try to avoid names with a space in it, as in some contexts, that can cause problems.

enter image description here

Answer 3

This is a classic use case for table joins: Enter layer properties of Table 1 > Tab Joins, create a new join (green + icon) and define the table to join and the attributes from both tables that match.

Options: select which fields to join and if the names of the joined fields should get a prefix:

enter image description here

enter image description here