9

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?

2 Answers 2

9

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.

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

1
  • That is a very good solution. The only thing is that I would like to make all the updates in the same layer (in your example , it would be the "interpolated points" layer). The reason for that is because I have a lot of settings saved it in this layer which are not imported to the new layer "Incremented". Is it possible in any way to retrieve data from the new ayer "Incremented" from the field calculator of the layer "Interpolated points?
    – Serggg182
    Nov 30, 2020 at 14:58
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)

enter image description here

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)

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.