6

I need to add a unique "ID" (using $ID or @row_number) but the problem is that I need to start from 1 to "...n" considering identical values from other field, then, when there is a different value, start counting from 1 again, if the value is repeated, write 2, 3, 4, 5 until finding new distinct values, and so on.

I have an image more explanatory than me, the red column is what I am getting, the green column is how I would like to add the numbers. I guess using the expression "count_distinct" could be useful, but I don't know how to structure it with my values.

example

Improve this question
3
11

We can use arrays for that:

array_find(
  array_agg(
    $id,
    "ReportID_2"
  ),
  $id
) + 1

With array_agg we have an array of all id grouped by the ReportID_2 values.

Then, array_find gets the position of the current id in the previous array, starting with the position 0.

Finally, we add 1 to get the positions from 1.

Improve this answer
8
  • 2
    Very nice and neet ! – J. Monticolo Jun 11 '20 at 13:15
  • 1
    @Geografisica, positions in the array are consecutive starting from zero. We are retriving the positions, not the $id or @rownumber. They are only the identification value to extract its position in the array. – Gabriel De Luca Jun 19 '20 at 21:05
  • 1
    @Geografisica, please, share a test layer in which I can reproduce your behavior, because in my layer I can't reproduce it. – Gabriel De Luca Jun 22 '20 at 14:17
  • 1
    @Geografisica, I understand that the difference is minimal and may go unnoticed, but you are not grouping by the value of the field "ReportID_2", but by the literal string 'ReportID_2', which makes all the rows enter the same group. The expression must be: "ReportID_2" || '_' || (array_find( array_agg( $id, "ReportID_2" ), $id ) + 1) – Gabriel De Luca Jun 23 '20 at 23:37
  • 1
    Great Gabriel, I works! now I got the perfect results!, yes, now I understood what was the problem. I really appreciate your help!. – Geografisica Jun 25 '20 at 15:26
5

Possible solution by means of PyQGIS.

Let's assume there is a point layer called "Layer_B" with it's attribute table, see image below.

input

Case 1. When the new "id" has to be assigned in a range of each attribute field (without grouping).

P.S. Many thanks to @J. Monticolo

Proceed with Plugins > Python Console > Show Editor and paste the script below

from PyQt5.QtCore import QVariant

fieldname = "info" #a field which is used as an aggregating factor 
newfieldname = "newid" #a new field that will maintain new ids

layer = iface.activeLayer()
idx = layer.dataProvider().fieldNameIndex(fieldname)

pr = layer.dataProvider()
pr.addAttributes([QgsField(newfieldname, QVariant.Int)])
layer.updateFields()

req = QgsFeatureRequest()
req = req.addOrderBy(fieldname)

attr_old = None

with edit(layer):
    for feat in layer.getFeatures(req):
        attr = feat.attributes()[idx]
        if attr == attr_old:
            i += 1
        else:
            i = 1
        feat[newfieldname] = i
        layer.updateFeature(feat)
        attr_old = attr

python_console_2

Press Run script run script and get the output that will look like

result_1

Case 2. When the new "id" has to be assigned every time when the value in another attribute field change (with grouping).

Proceed with Plugins > Python Console > Show Editor and paste the script below

from PyQt5.QtCore import QVariant

fieldname = "info" #a field which is used as an aggregating factor 
newfieldname = "newid" #a new field that will maintain new ids

layer = iface.activeLayer()
idx = layer.dataProvider().fieldNameIndex(fieldname)

list_attributes = []

for feat in layer.getFeatures():
    list_attributes.append(feat.attributes()[idx])
    
list_attributes = list(set(list_attributes))

dct = {list_attributes[i]: i + 1 for i in range(len(list_attributes))}

pr = layer.dataProvider()
pr.addAttributes([QgsField(newfieldname, QVariant.Int)])
layer.updateFields()

with edit(layer):
    for feat in layer.getFeatures():
        attr = feat.attributes()[idx]
        for key, value in dct.items():
            if attr == key:
                feat[newfieldname] = value

        layer.updateFeature(feat)

python_console_1

Press Run script run script and get the output that will look like

result_2

References:

Improve this answer
3
  • 2
    update your code, here you give the same newid for the same info value. The author wish a new increment each time the info change. – J. Monticolo Jun 11 '20 at 13:09
  • 1
    @Taras I'm very happy to have found this solution, for I can make really good use of the "Case 2" for my work. It would be perfect if there would be a possibillity to sort the numbering of the column "newid" by another field, like "date", so the "earlier" features would get the lower number. Any help would be highly apreciated. – Bjoern Sep 28 '20 at 13:40
  • Please ask it as a new question – Taras Sep 28 '20 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.