Creating new field considering duplicate and numeric values in QGIS

Question

I'd like to create a new field considering duplicate and numeric values.

For example, I have an attribute table with duplicate values in the "field1" and numeric values in the "field2". I'd like to create a "field3" in which there are the suffix _1, _2...on the "field1" attributes, when they are duplicates and when "field2" > 0.

In case "field2" = 0 or not duplicated value in the "field1" -> "field3"="field1".

The suffix should not be set randomly, but following the spatial direction (or left or right); and for each group of duplicated values.

Before:

After:

Any suggestion to use a string in the field calculator to do this in QGIS?

Answer 1

You can try the following expression:

with_variable(
    'my_array',
    array_agg(
        $geometry,
        group_by:="field1",
        filter:="field2">0,
        order_by:=x($geometry)
        ),
    CASE
        WHEN array_find(@my_array, $geometry)=-1
        THEN to_string("field1")
        ELSE to_string("field1") || to_string(array_find(@my_array, $geometry) + 1)
    END
    )

A short explanation:

array_agg takes all geometries into an array with the same value in "field1" (the GROUP_BY), without the ones having a 0 in "field2" and order it by the easting value(x). In the CASE WHEN expression it will proof, if the current value (geometry) is in the array, if yes, it will be added by a position value from the array, if not, it will be nothing added. You may have to play around with the conditions a bit to get the result you want to achieve.