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, 2022 at 12:15
  • @geozelot would you please review my comments below? May 11, 2022 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, 2022 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, 2022 at 13:01
  • 1
    @JoseLuisLandivarS Ya, of course you'll have to replace those with your actual column names...
    – geozelot
    May 11, 2022 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, 2022 at 15:11
  • 1
    @JoseLuisLandivarS Yes, it needs a CAST to INTEGER - I updated the query.
    – geozelot
    May 11, 2022 at 19:59

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .