# Calculating new id with suffix for another id field that has duplicates in QGIS

I have a linear dataset that has a unique `"ID"` for every row, but many of the linear features needed to be segmented. After segmenting, there are some rows that were split into two, three, four+ segments. I want to create a new unique `"ID"` based off of the old `"ID"` field that would end up being:

``````[new id] = [old id] + [A, B, C, etc.]
``````

I need a way to field calculate the new id that will detect duplicates in the old id and add the correct letter.

Order doesn't particularly matter, but if there's a way to make it order based off some value like fid, that'd be great.

Something like this:

``````[fid] [old id] [new id]
[1]   [1]      [1 (or 1A)]
[2]   [2]      [2A]
[3]   [2]      [2B]
[4]   [3]      [3A]
[5]   [3]      [3B]
[6]   [3]      [3C]
``````

This GIS SE article Creating new field considering duplicate and numeric values gets close but it doesn't quite hit the mark. I have a feeling that `array_agg` function is the key, but I don't understand it and the docs didn't really help.

This should do it:

``````"old_id" || char(array_find(array_agg("fid","old_id"),"fid")+65)
``````

Note that `char()` returns the character associated with a unicode code. So if you have more than 26 parts you will end up behind `Z`, starting with `[`. See this list: https://en.wikipedia.org/wiki/List_of_Unicode_characters. `65` returns `A`, so the first part will get an `A` appended.

In case you have more than 26 parts, you could use a custum function, such as:

``````from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom', referenced_columns=[])
def num_to_char(n, feature, parent):
x = 'a'
if n < 1:
raise ValueError("Number must be positive")
result = ""
while True:
if n > 26:
n, r = divmod(n - 1, 26)
result = chr(r + ord(x)) + result
else:
result = chr(n + ord(x) - 1) + result
return result.upper()
``````

Original code from https://stackoverflow.com/a/42176641/8947209, slightly modified.

You could then use as follows:

``````"old_id" || num_to_char(array_find(array_agg("fid","old_id"),"fid")+1)
``````

It will return an Excel-Like-Style-Letter-Numbering, e.g. returning `B` for `2`, `AA` for `27`, or `DW` for `127` or `BUEEGOCSIDMEII` for `6985412563221548751`. It will return an error for 0 or negative numbers.

How `array_find(array_agg("fid","old_id"),"fid")` works:

1. First you generate an array of your `"fid"`'s, grouped by the `"old_id"`'s. In your example this will return `[1]` for your first feature, `[2,3]` for your second and third and `[4,5,6]` for your fourth, fifth and sixth on. The grouping prevents the array from containing `"fid"`'s that have a different `"old_id"`.
2. Search for the current `"fid"` by using `array_find()` in that array and return its index in the array. This ensures, that each new group starts at 0. Arrays are 0-based. This requires to add `+1`, if you want to start at 1. So for your first fid it will return 0. For your second 0. For your third 1. For your fourth 0. For your fifth 1. For your sixth 2.

Just try it out one by one. I guess try-by-your-own-example explains it better than my words.

• Thank you very much it worked, do you think you could explain to me how array_find(array_agg) works in this instance? Maybe the way you explain it will finally be the thing that makes those functions click in my mind. Aug 25, 2023 at 21:15