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

Question

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)

Answer 1

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