QGIS – Creating ‘fid’ Column Using Field Calculator

attribute-tableexcelexpressionfield-calculatorqgis

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
enter image description here

The Sort and Number plugin also does it incorrectly:
enter image description here

Best Answer

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
Related Question