10

I have two columns in my QGIS attribute table. The first column contains values that aren't contained the second column and vice versa. I would like to add a new field that contains both the values from the first and second column. I thought it might be as simple as "Value 1 + Value 2) but this just gives me Null results. All values are strings.

Value 1| Value 2 | New Column
-------------------------
 Bacon |         | Bacon
 Eggs  |         | Eggs
       | Cheese  | Cheese
       | Ham     | Ham  
2
  • It is slightly ambiguous from the wording of the question if you want to concatenate: "I would like to add a new field that contains both the values from the first and second column." would indicate concatenation but in your example you don't have occurences of both fields having values. Which is it?
    – Gabriel
    Jun 29, 2018 at 13:28
  • @GabrielC. Both columns don't have values, wherever one column has a value the other column will not. I hope this helps.
    – Dunuts
    Jun 30, 2018 at 4:44

5 Answers 5

32

Many operators and functions in SQL (and therefore expressions) return NULL if one of the parameters was NULL

The following examples demonstrate the behavior of various operators on a layer with the columns A and B.

"A" + "B"

  • NULL + 'text'NULL
  • 'a' + 'b''ab'

"A" || "B"

  • NULL || 'text'NULL
  • 'a' || 'b''ab'

CONCAT("A", "B")

  • CONCAT(NULL, 'text')'text'
  • CONCAT('a', 'b')'ab'

COALESCE("A", "B")

  • COALESCE(NULL, 'text')'text'
  • COALESCE('a', 'b')'a'
  • COALESCE('a', NULL)'a'
  • COALESCE(NULL, NULL, 'Other')'Other'

In your case you want to work with either CONCAT or COALESCE depending on the expected behaviour with multiple / no values.

1
  • 1
    It's too bad this isn't the accepted answer, I think this is the best one as it explains how the different operators behave, creating the problem mentioned in the OP.
    – Gabriel
    Jun 30, 2018 at 13:02
17

You could use field calculator and follow these steps:

1- Create new field (string)

2- Use "Coalesce" Function

       coalesce(  "Value 1" , "Value 2" , 'value if No data')

Colaesce function returns the first not NULL

enter image description here

3

Select the layer in the layer panel and open the python console and run this snippet:

layer = iface.activeLayer()
layer.startEditing()
fields = layer.pendingFields()
fieldIndex = fields.indexFromName('newColumn')
for feature in layer.getFeatures():
    if feature['value1']:
        layer.changeAttributeValue(feature.id(),fieldIndex,feature['value1'])
    if feature['value2']:
        layer.changeAttributeValue(feature.id(),fieldIndex,feature['value2'])
    if feature['value1'] and feature['value2']:
        layer.changeAttributeValue(feature.id(),fieldIndex,feature['value2'] + ' ' + feature['value2']) # not sure what you want to do here if values found in both value1 and value2 fields
layer.commitChanges()
3

You also could use the field calculator, add a new field and feed him the following

CASE WHEN "column1" IS NULL
THEN "column2"
ELSE "column1"
END
2

Easy way to do a similar thing is by combining column values in a new field e.g. in field calculator (QGIS):

"field_name1" + ',' + "field_name2"

results in new field with combined values from 2 columns/fields separated by a comma. Note that both fields must be of type String, or use to_string("field_name1") + ',' + to_string("field_name2") if they are not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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