9

Here is the thing, 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?

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

  • 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 '20 at 14:58
1

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.