9

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  
  • 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 C. Jun 29 '18 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 '18 at 4:44
19

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
    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 C. Jun 30 '18 at 13:02
14

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()
2

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

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.