3

In my attribute table, I have 9 columns providing information on the same thing (group ID). One row does only have one group ID. I.e. one row has a group ID in one of these 9 columns, in the other columns it says 0. I need to have one column with the group ID information (an 8-10 digit number) for all rows. I have tried coalesce function, but it is (in it's pure form) not the right function for this, as each column has a number (i.e. 0 if not a group ID). Thus, the coalesce function provides me with the exact same column as the first stated column.

Is there any way to use the coalesce function but with a condition? E.g. something like:

Coalesce ("Group_ID" if > 0, "Group_ID_1" if > 0, "Group_ID_2" if > 0, "Group_ID_3" if > 0 )

Or, is there any other way to do this?

Improve this question
0

4 Answers 4

Reset to default
3

coalesce can also work with expressions (as in the tooltips on each expression readable).

So, the following should work:

coalesce(nullif("Group_ID",0),nullif("Group_ID_1",0),nullif("Group_ID_2",0),...)

nullif() is a new expression, it checks whether a value (here the ID) equals another value (here 0) and returns NULL, if this is the case. Hence for all ID = 0 coalesce is returned NULL and looks at the next column.

Improve this answer
3

The other solutions are better as they could be used in other case (if you have to deal with text column for exemple) but in this specific case as it's presented (a non 0 number Group_ID in one column and 0 in all the other) the simplest way is just a sum :

Group_ID_1 + Group_ID_2 + ... + Group_ID_8 + Group_ID_9

as adding several time 0 to your number ID won't change it....

Improve this answer
2

This expression may work;

array_to_string(array_filter(array("Group_ID", "Group_ID_1", "Group_ID_2", "Group_ID_3"), @element <> '0'))

When your ID fields ("Group_ID_1"...) are defined as numbers (e.g. Integer);

enter image description here

... it works as we have expected. However, the advantage of this approach will be seen when your ID fields type is Text.

enter image description here

Improve this answer
1

(A much less elegant solution than the other answers but the code structure may be useful for other problems)

You can use the python console:

#Change these two lines to match your data
layername = 'New scratch layer'
field_to_update = 'idcol'

layer = QgsProject.instance().mapLayersByName(layername)[0]

valuefields = [f.name() for f in layer.fields() if f.name().startswith('Group')] #List all fields starting with Group
#valuefields  will be ['f1', 'f2', 'f3'] with my test data (I had f.name().startswith('f'))

with edit(layer):
    for feature in layer.getFeatures():
        newval = max([feature[fieldname] for fieldname in valuefields if feature[fieldname] is not None], default=None) #If no max value is found, None (NULL) is used
        feature.setAttribute(feature.fieldNameIndex(field_to_update), newval)
        layer.updateFeature(feature)

enter image description here

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.