Adding auto incremented value to existing ID

Question

I am having a table where two columns are "ID" and "maintenance_ID". The "ID" column is unchangeable and comes from another table but the "maintenance_ID" is calculated from other fields like the "Date".

Example:

ID     Date        maintenance_ID 
EH001  21/11/2020  EH001M20201121
EH001  21/11/2020  EH001M20201121
EH002  21/11/2020  EH002M20201121

To create the "maintenance_ID" I am using the following code:

"ID" + 'M' + format_date("Date", 'yyyyMMdd')

What I would like to do is adding to this formula a parameter where generates a new number at the end of the "maintenance_ID" to make it unique. As you can see in my example the two first records are the same so I would like to add a "0" to the first one and a "1" to the second one and so on until 9 ( I won't be more than 9 records with the same "ID". Example:

maintenance_ID
EH001M202011210
EH001M202011211
EH002M202011210

Anyone knows what can I add to my expression to achieve this?

Answer 1

Assuming "maintenance_ID" field is already given, I would use Add autoincremental field tool (in Processing Toolbox > Vector table) to prepare a temporary field, then put them together.

(1) Start Add autoincremental field and select maintenance_ID as the Group value.

(2) The tool will return a new layer Incremented with a new "AUTO" field.

(3) Use the Field Calculator to update "maintenance_ID" field. The expression is concat("maintenance_ID", "AUTO").

(4) Examine the updated attribute table, and delete "AUTO" temporary field.

Answer 2

You can also use python if that is an option:

from collections import defaultdict
layer = iface.activeLayer()
fn = 'kkod'

d = defaultdict(list)
for f in layer.getFeatures():
    d[f[fn]].append(f.id())
#d is now a dictionary of each group and all features ids that belong to that group:
#   351: [15, 16, 20, 23, 33, 42, 50, 52, 53, 58, ... 
#   421: [24], 
#   332: [65, 103, 157]})

inc = {}
for kkod, valuelist in d.items():
    for e, id in enumerate(valuelist):
        inc[id] = e
#inc is now each features id and cumulative count, for example
# {0: 0, 1: 1, 2: 2, 4: 3, 13: 4, ...

with edit(layer):
    for f in layer.getFeatures():
        f['kkod2'] = '{0}_{1}'.format(f['kkod'], inc[f.id()]) #Change this line
        layer.updateFeature(f)

And if you have pandas module (try import pandas) you can simplify it to:

import pandas as pd
layer = iface.activeLayer()
fn = 'kkod'

data=[f[fn] for f in layer.getFeatures()]
df = pd.DataFrame(data, columns=[fn])
give_count= iter(df.groupby(fn).cumcount()) #https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.cumcount.html

with edit(layer):
    for f in layer.getFeatures():
        f['kkod2'] = '{0}_{1}'.format(f['kkod'], next(give_count)) #Change this line
        layer.updateFeature(f)