QGIS Attribute Table – Compare Strings and Extract Unique Values

arrayattribute-tableqgisstring

In QGIS 3.18. I am looking for a field calculator expression able to return the values of a string (comma separated) in Field B which are not* also values of a string in Field A. For example:

Field A: 2,4,6,8,10
Field B: 1,2,3,4,5
Field C (desired result of expression): 1,3,5

I suppose this will be a situation for string_to_array and then an array function, but array_distinct returns distinct values of one single array; perhaps there is a way to structure the expression such that it will compare the 2 cells, but I've not yet found it.

Best Answer

Use this expression:

array_to_string (
    array_remove_all(
        array_foreach (
            string_to_array(field_B),
            if (
                array_contains (string_to_array(field_A),@element),
                '',
                @element
            )
        ), 
        ''
    )
)

Related Solutions

QGIS – How to Merge Attributes from Overlapping Polygons into One Polygon in QGIS

If you are using QGIS2.18, concatenate() function in Aggregate group may be helpful.

Suppose I have a polygon layer of 5 duplicated stars (id: 1-5) and one rectangle (id:10) and each stores value I want in myvalue field.

I open Field Calculator and create a new text field by an expression concatenate( to_string("myvalue") , group_by:= geom_to_wkt($geometry), concatenator:= ', ') to store all myvalue from the overlapping features.

Then my new field has the same texts if the geometries are identical.

Remove duplicates. I like MMQGIS plugin's `Delete Duplicate Geometries' but there would be various ways.

Done. Maybe I will delete myvalue field later.

QGIS – Expression to Aggregate Values from an Array

Note that if "idTargetLayer" in mySourceLayer already is an array, you need to remove string_to_array() for this field, see comment by OP below.

Option 1 - Independent "ID", but more complex expression

If your IDs are numerical but have nothing to do with the featureIDs of the layer, you can use this, a little more complex, expression:

array_to_string(
    array_foreach(
        array_foreach(
            array_foreach(
                string_to_array("idTargetLayer",', '),to_int(@element)), -- create an integer array of the values to search for
                    array_find(
                        array_foreach(
                            aggregate('myTargetLayer','array_agg',"idTargetLayer" || '|' ||"myField"), -- create an array of the IDs and myField values, intended to work similar to a Python-Dictionary, | is the unique separator between key and value
                        regexp_substr(@element,'[0-9]*')), -- extract the IDs of this 'Dictionary'
                    @element -- get the index of the value we will extract later
                    )
        ),
        array_get(
            array_foreach(
                aggregate('myTargetLayer','array_agg',"idTargetLayer" || '|' ||"myField"), -- create an array of the IDs and myField values, intended to work similar to a Python-Dictionary, | is the unique separator between key and value
            regexp_substr(@element,'[0-9][|](.*)')),  -- extract the Values of this 'Dictionary'
        @element) -- get the current integer array element of the value dict
    ),
',')

Its a little hard to explain, and even confusing myself, but I hope you get the idea of it. However, as the result shows, its working fine:

Option 2 - "ID" is related to layers featureID and easier expression

The following expression should work if the id's of myTargetLayer are the layer's featureID's:

array_to_string(
    array_foreach(
        array_foreach(
            string_to_array("idTargetLayer"), -- idTargetLayer is a string so we need to convert it to an array first
            to_int(@element)), -- idTargetLayer is a string field, so we need to convert the numbers to integer before we can look it up
    attributes(get_feature_by_id('myTargetLayer',@element))['myField'] -- get the attribute of ['myfield'] of the layer 'myTargetLayer' for the featureID '@element', where @element is the extracted integer of 'idTargetLayer' array
    )
,',') -- convert the gathered array back to a string

Best Answer

Related Solutions

QGIS – How to Merge Attributes from Overlapping Polygons into One Polygon in QGIS

QGIS – Expression to Aggregate Values from an Array

Related Question