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

2
  • @Taras I checked it, but it is not the same since the numbers do not need to start counting from 1 again. May 11 at 12:15
  • @geozelot would you please review my comments below? May 11 at 13:59

1 Answer 1

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
6
  • Using DB Manager I get the error: Query preparation error on PRAGMA table_info(_tview): no such column: id2 May 11 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) May 11 at 13:01
  • 1
    @JoseLuisLandivarS Ya, of course you'll have to replace those with your actual column names...
    – geozelot
    May 11 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 at 15:11
  • 1
    @JoseLuisLandivarS Yes, it needs a CAST to INTEGER - I updated the query.
    – geozelot
    May 11 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.