Calculating new attribute based on condition using Field Calculator in QGIS

Question

I have a shape with different city areas that contains the average building heights ("HHO-mean") in meters. Now I want to categorize the average building heights in a new column in five different categories (1-5).

I tried the following:

CASE
    WHEN "HHO_mean" > 5.7 THEN 1
    WHEN "HHO_mean" > 6.4 THEN 2
    WHEN "HHO_mean" > 7.1 THEN 3
    WHEN "HHO_mean" > 8.0 THEN 4
    WHEN "HHO_mean" > 10 THEN 5
END 

and

if( "HHO_mean" > 5.7, '1', if( "HHO_mean" < 6.4, '2',  if( "HHO_mean" < 7.1, '3', if( "HHO_mean" < 8.0, '4', if( "HHO_mean" < 10, '5', '0')))))

In both cases the new column only contains 1 in every field. Any suggestions?

Answer 1

In your query you forgot to include the upper edge for your range of values. It is possible with using an AND-operator.

So, try:

CASE
   WHEN "HHO_mean" > 5.7 AND "HHO_mean" <= 6.4 THEN 1
   WHEN "HHO_mean" > 6.4 AND "HHO_mean" <= 7.1 THEN 2
   WHEN "HHO_mean" > 7.1 AND "HHO_mean" <= 8.0 THEN 3
   WHEN "HHO_mean" > 8.0 AND "HHO_mean" <= 10 THEN 4
   WHEN "HHO_mean" > 10 THEN 5
   ELSE 0
END

The same issue appears in if(), therefore it has to be if("HHO_mean" > 5.7 and "HHO_mean" < 6.4, '1', ....

Note: The BETWEEN-operator unfortunately is not implemented in QGIS's Field Calculator.

---

References:

• SQL Comparison operator

Answer 2

the order of the condition check is value_if_true then value_if_false

so in your second test, it should be (note the >= instead of > on the first condition):

If( "HHO_mean" <= 5.7, '0', if( "HHO_mean" <= 6.4, '1', if( "HHO_mean" <= 7.1, '2', if( "HHO_mean" <= 8.0, '3', if( "HHO_mean" <= 10, '4', '5')))))

Answer 3

The case will end when first when is true, so with your case expression all values above 5.7 will be classified to 1. You just need to change the order of the whens from highest to lowest:

CASE
    WHEN "HHO_mean" > 10 THEN 5
    WHEN "HHO_mean" > 8.0 THEN 4
    WHEN "HHO_mean" > 7.1 THEN 3
    WHEN "HHO_mean" > 6.4 THEN 2
    WHEN "HHO_mean" > 5.7 THEN 1
END