Using QGIS field calculator to create an 'fid' column according to a value from another column

Question

I have a shapefile with 1,800 rows. I need to edit this file as follow:

• Row_name is from 1 to 45
• Col is from 1 to 39
• fid should be from 1 to 1800 in order (This is the new field I need to create).

I tried using $id from 'Record and Attributes', but for some reason it starts with fid 1 at row_5 instead of row_1.

How can I use the field calculator in QGIS to do so? I wish it were like Excel where I can just copy and paste or use the corner dropdown tool. Can you write a QGIS expression to be used in the field calculator?

Example:

Row_name    Col     fid
row_1       1       1
row_1       2       2
row_1       3       3
row_1       4       4
...
row_1       39      39
row_2       1       40
...
row_2       5       44
...
row_3       1       79
row_3       2       80
row_3       3       81
...

I tried using @row_number, but the same problem happens. The value should be dependent on row_#. This question is not the same as Filling column with consecutive numbers in QGIS

The Sort and Number plugin also does it incorrectly:

Answer 1

Hacky solution using the field calculator:

array_find(
  array_agg(
    expression := to_int(substr("Row name", 5))*10*maximum("Col")+"Col",
    order_by   := to_int(substr("Row name", 5))*10*maximum("Col")+"Col"
  ),
  to_int(substr(attributes()['Row name'], 5))*10*maximum(attributes()['Col'])+attributes()['Col']
) + 1

---

Probably the most sane solution:

• drop column fid
• within the DB Manager navigate to Virtual Layers | Project layers
• run

  SELECT ROW_NUMBER() OVER(ORDER BY CAST(SUBSTR("Row name", 5) AS INT), CAST("Col" AS INT)) AS fid, *
  FROM   <your_layer_name>
  ;
  

• create new Virtual Layer from the result
• Save as... your preferred file type