4

I am trying to compare the contents of two columns named C1 and C2, each of them having some 250 values. I want to create another field, named C3, with TRUE if a value from C1 is found anywhere in C2, or FALSE, if not.

I found something which is very close, but it works only for the same entry in each column.

I am using QGIS 2.12 and I am curious if there is any SQL expression that I can use.

edit: C1 is called NAME, C2 is DIST_ORIG

enter image description here

  • 1
    Can you post some sample data? – Nathan W Nov 9 '15 at 13:24
  • 2
    Just a thought: duplicate the layer, and join them on NAME=DIST_ORIG might give you the rows that can be set to TRUE. – AndreJ Nov 9 '15 at 14:02
  • In what format is the data? SQL expressions only pertain to database sources. – Vince Nov 9 '15 at 14:57
  • @AndreJ your advice proved to be the solution. Thank you very much. – Cezar B Nov 9 '15 at 16:42
3

A simple way in QGIS:

  1. duplicate the layer
  2. join them on NAME=DIST_ORIG

The remaining rows are those that can be set to TRUE.

You might stumble on typos, trailing blanks etc.

| improve this answer | |
3

Here's an expression which will do the trick - just substitute your actual layer name below:

case when
attribute(get_feature('YOUR_LAYER_NAME','DIST_ORIG',"NAME"),'DIST_ORIG') then true
else false
end

To break this down:

get_feature('YOUR_LAYER_NAME','DIST_ORIG',"NAME")

this part will fetch the first feature from your layer where the DIST_ORIG value matches "NAME" (eg, the value in the "NAME" column for the current feature). If there's no matching features found, nothing will be returned by this function.

We then need to do a little trick to convert this to a condition we can use in the CASE WHEN / ELSE / END statement. So I've wrapped the get_feature call in a attribute function, which will return the value in the specified field from the feature returned by get_feature. So this will either be a value from "DIST_ORIG" if a feature was found, or NULL if no feature was found.

Then the CASE WHEN / ELSE / END is just used to directly convert the result to a true/false value.

| improve this answer | |
1

You can also use a custom function in the field calculator.

from qgis.core import *
from qgis.gui import *

@qgsfunction(args="auto", group='Custom')
def isInColumn(value1, feature, parent):
    return 0 if value1 == None else 1

In the field calculator you use the function with the result of the getFeature() expression like this:

 isInColumn(getFeature( 'YOUR_LAYER_NAME', 'COLUMN_TO_SEARCH_IN', "VALUE_FROM_SEARCH_COLUMN"))

If the function isInColumn() gets a valid Feature, it Returns '1' (true) otherwise '0' (false). So you can update the third column.

| improve this answer | |
  • Note that I've actually fixed this in QGIS itself now - so you can use the results of get_feature() or geometry() directly as a boolean criteria. This will be available from versions >=2.14. – ndawson Nov 10 '15 at 21:24

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.