Creating new field considering duplicate and numeric values

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.