Compare two columns using Graphical Modeler in QGIS

Question

I have a layer with several columns. One of them is called 'cod' and the other is called 'cod_2'. 'cod_2' is null, except for one row. I want to know if the numerical value of 'cod_2' is present in any row of 'cod'. I'll give you an example.

Cod Cod_2
1     NULL
1     NULL
18    NULL
3     NULL
42    18
42    NULL
7     NULL

18 is present in both columns, but not in the same row. So, I can't use

if("col"="col_2",1,0)

because it will return 0.

I've tried this solution (Comparing Column Contents in QGIS) but it's not working because I'm working with Graphical Moduler, thus I can't call the layer name. I've tried to use @layer_name but it didn't worked either.

Answer 1

This is a job for an Aggregate function.

Use array_agg to combine all the values in "cod_2" into an array.

array_agg("cod_2")

Now that the "cod_2" values are in an array, we can use the Array functions. In this case, use array_find(array,value) to find the "cod" value in the array.

array_find(array_agg("cod_2"),"cod")

• If the "cod" value is in the array, the output will be a non-negative integer.
• If the current "cod" value is not in the array, the output will be -1.

Now we can write a true/false expression to test whether the current "cod" number is in the "cod_2" column.

array_find(array_agg("cod_2"),"cod")>-1

• If the current "cod" value is in the "cod_2" column, the output will be 1.
• If the current "cod" value is not in the "cod_2" column, the output will be 0.

If you want outputs other than 1 for true and 0 for false, use an if statement.

if(array_find(array_agg("cod_2"),"cod")>-1, result when true, result when false)