2

I have a shapefile with a categorical field (cat) and an integer field (value) in QGIS.

I want to create a new field (group), that groups incremental values per category until there is a break/gap into a new 'group' field. The group field needs to a unique integer per group - this field does not need to be incremental.

Below is an example of what I am after, I would need to generate the 'group' field as follows:

cat value group
1 0 1
1 1 1
1 2 1
1 4 2
1 5 2
2 0 3
2 1 3
2 2 3
3 3 4

is there a way to do this using field calculator?

0

2 Answers 2

4

You can achieve this using a virtual layer

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query and adjust for your layer name.

SELECT *, 
       SUM(CASE WHEN group_break THEN 1 END) 
         OVER (ORDER BY cat,val) AS group_id
FROM (
SELECT *,
    COALESCE(
     (val-1 = LAG(val) 
        OVER (PARTITION BY cat ORDER BY val))
    ,FALSE) IS FALSE AS group_break
FROM myLayer) sub;

Let's break it down: First, in the sub subquery, we identify rows that should get a new group id. Do do this, we break the table by cat value, then order it by val and if the previous (lag()) value is null (i.e it was for a different cat) OR not equal to the current value -1, then we know it is the 1st of a new group.

Then, in the outer query, we compute a running sum, with a value of 1 for rows corresponding to new groups, and 0 otherwise. Because we order again by cat and val, the running total becomes the new group_id you are looking for.


Using the input you have provided, here it what is generated:

enter image description here

2
  • 1
    Nice use of Virtual Layers! For future reference, The input layer's name (you used myLayer) must match the name used in the QGIS table of contents, and that name cannot contain any spaces.
    – Stu Smith
    Commented Feb 6, 2023 at 17:15
  • Thank you, this answer solved my question. My data was too big to run efficiently with virtual layers so I adapted this code using Pgadmin4 instead.
    – JUNGLE
    Commented Feb 6, 2023 at 23:13
2

Use this expression with Field Calculator:

with_variable(
    'arr',
    array_agg (
        if (
            cat = attribute (get_feature_by_id (@layer, $id+1), 'cat') and 
            value + 1 = attribute (get_feature_by_id (@layer, $id+1), 'value'),
            0,
            $id
        )
    ),
    array_find (
        array_filter (@arr, @element >0),
        array_filter (@arr, @element >= $id)[0]
    )
)+1

The expression is based on the one explained here, but does it in one step.

enter image description here

1
  • This code will work on pre-sorted data, but it will generate unexpected results if the data isn't sorted.
    – bixb0012
    Commented Feb 6, 2023 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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