3

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

Jose Luis Landivar S
  • 456
  • 2
  • 12

1 Answers1

6

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
geozelot
  • 30,050
  • 4
  • 32
  • 56
  • Using DB Manager I get the error: Query preparation error on PRAGMA table_info(_tview): no such column: id2 – Jose Luis Landivar S May 11 '22 at 13:01
  • Using Field Calculator I get the error: Eval Error: Could not calculate aggregate for: to_int(substr("id1", 5, NULL)) * 10 * maximum("id2", NULL, NULL) + "id2" (Field 'id2' not found) – Jose Luis Landivar S May 11 '22 at 13:01
  • 1
    @JoseLuisLandivarS Ya, of course you'll have to replace those with your actual column names... – geozelot May 11 '22 at 15:07
  • 1
    @JoseLuisLandivarS ...and to make that clear, the 5 in both versions of the substr[ing] functions needs to match the position of the first number within the string in your "Row name" values. – geozelot May 11 '22 at 15:11
  • I tried using "SELECT ROW_NUMBER() OVER(ORDER BY SUBSTR(Col, 8), Row) AS fid, * FROM '2022_da_wheat_irr_boundary';", but row-10 Col 1 gets 40. 40 should be row-2 Col 1. Any suggestions? – Jose Luis Landivar S May 11 '22 at 19:30
  • 1
    @JoseLuisLandivarS Yes, it needs a CAST to INTEGER - I updated the query. – geozelot May 11 '22 at 19:59