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

Question

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.

Answer 1

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.