# Is it possible to calculate a value based on values of another row?

I have a point shape file in qgis. For one of the attributes, I would like to offset the values, i.e. the value for point 1 goes to point 10, the value for point 2 goes to point 11 and so on. I see the \$rownum attribute in the field-calculator, but I cannot see any examples on how (if possible at all) I can use that to access values in another row...

Is this possible using the field calculator or do I have to make a python script?

(Yes, I know for the example, rows 1 - 9 will get null- values and the last 9 values will "fall off the end")

(Using qgis 2.0.2)

• Are there any special attributes for the entities (rows) to which you wish to shift the value? If so, you might use select by attributes and export them to a new layer than you can use join and calculator to shift them. It is still a long operation by I believe it's shorter than exporting the table and re-importing it after performing modifications. Nov 22 '13 at 9:00
• No, it is for all points, just as @HasT said below, it is easy to do in Excel - if it was not for the hassle of exporting and importing. Nov 22 '13 at 11:20

Solution 1. QGIS&Excel Create new column (objectnumber) in shapefile with unique values (\$rownum). Export attribute table of point layer to dbf/ or csv file. In Excel copy values in new column and shift them (for point 1 goes to point 10, the value for point 2 goes to point 11 and so on). Then Join dbf/ or csv table in qgis to shapefile by common field (objectnumber).

Solution 2. PostGIS Example of data:

``````CREATE TABLE test(nomer int, nameold TEXT);
INSERT INTO test(nomer, nameold) VALUES(1,'Aaaa');
INSERT INTO test(nomer, nameold) VALUES(2,'Xfff');
INSERT INTO test(nomer, nameold) VALUES(3,'Gyyy');
INSERT INTO test(nomer, nameold) VALUES(4,'Nuuu');
INSERT INTO test(nomer, nameold) VALUES(5,'Rrrr');
INSERT INTO test(nomer, nameold) VALUES(6,'Kloo');
INSERT INTO test(nomer, nameold) VALUES(7,'Ferr');
INSERT INTO test(nomer, nameold) VALUES(8,'Btyy');
INSERT INTO test(nomer, nameold) VALUES(9,'Gatt');
INSERT INTO test(nomer, nameold) VALUES(10,'Vety');
``````

Query for shift rows:

``````SELECT a.nomer,a.nameold,b.nameold AS namenew FROM test a LEFT JOIN test b ON a.nomer=b.nomer+4
``````

or

``````SELECT *, lag(nameold, 4) OVER(ORDER BY nomer) AS namenew FROM test;
``````

Result:

``````nomer nameold namenew
-------- ---------- ----------
1 Aaaa (null)
2 Xfff (null)
3 Gyyy (null)
4 Nuuu (null)
5 Rrrr Aaaa
6 Kloo Xfff
7 Ferr Gyyy
8 Btyy Nuuu
9 Gatt Rrrr
10 Vety Kloo
``````
• That's what I have been doing, the problem is just that I need to do this a lot of times and then it ends up with a lot of hazzle to go back and forth between qgis and excel (Excel cannot just update a file that is held open by qgis...) Nov 21 '13 at 11:29
• @MortenSickel, add solution in PostGIS. I hope this will help you Nov 25 '13 at 20:36