[GIS] Why are QGIS field calculator statements (both IF and CASE) giving strange results

field-calculatorif statementqgis

Using the field calculator, I'm trying to update an attribute only if it equals to zero:

CASE WHEN  "Z" = 0
THEN
"Z" =  rand(1,100)
END

This let the 0 values as they are and updates all others to "NULL".

This IF statement doesn't work either:

IF( "Z" = 0,
"Z" = rand(1,100),
"Z" = "Z"
)

It sets all values greater than zero to 1 and let the others = 0.

What's wrong with these statements?
Z is a double.
I clicked all functions and element in the search list so there shouldn't be any typo.

And what kind of language is this, I can't recognize SQL; it seems to be some kind of "internal to QGIS language".

Best Answer

The value that is written into the attribute table depends on what field calculator returns. In your case:

CASE WHEN  "Z" = 0
THEN "Z" = rand(1,100)
END

means that it compares Z and 0, and if that's true, it compares Z and a random value from 1 to 100, which has to be false because Z is 0. That's why it writes 0 (=False) into the fields which were 0. And the expression doesn't return anything for all other input values. That's why it writes NULL everywhere else.

This means, that you have to change the expression as follows:

CASE WHEN  "Z" = 0
THEN rand(1,100)
ELSE "Z"
END

Afaik, this is in line with SQL as interpreted by Postgres, which also supports this kind of CASE statements.

The IF statements were introduced because users wanted something more Excel-like. Your example is failing for the same reason stated above.

Related Question