Sort attribute table and perform sequential calculation using Field Calculator

Question

I have an attribute table with a Time attribute (UTCtime) which I want to use to create markers which I will later use to split the features. Example of table below. I will do this by taking the difference between UTCtime at row 2 and subtracting the UTCtime from row 1. When I have a value >1 I want it to put a character like $ in a column so I can split the features at this point and/or group them.
How can I create the Diff column in QGIS and avoid getting the inevitable weird value at the end of the table (-1305510005.40)?
UTCtime   Group  Diff   
1305509644  1   0.10
1305509644  1   0.00
1305509644  1   0.10
1305509644  1   0.00
1305509644  1   0.10
1305509645  1   0.00
1305509645  1   0.10
1305509645  1   0.00
1305509645  1   360.40
1305510005  2   0.00
1305510005  2   0.10
1305510005  2   0.00
1305510005  2   0.10
1305510005  2   0.00
1305510005  2   0.10
1305510005  2   0.00
1305510005  2   0.10
1305510005  2   -1305510005.40

Babel · Accepted Answer

The formula to substract UCTime-value of the next entry from the UCTime-value of the current entry is:
attribute (
    get_feature_by_id(
        @layer, 
        $id+1)
    , 'UCTime')-
"UCTime"

The last entry is a NULL (no data) value as there is no next entry to substract from.
See screenshot for results:

Your additional question in the comment about sorting: you can create a new field sort that creates a number that orders your values: 1 for the smallest, 2 for the next higher etc.:
array_find( 
    array_distinct(
        array_sort ( 
            array_agg( 
                UCTime
            ),
            true
        )
    ),
    UCTime
)+1

Sort attribute table and perform sequential calculation using Field Calculator

One Answer

Add your own answers!

Ask a Question