# Retrieve value from another layer by field calculator

I have two vector layers, one point layer named "element_ep", and another layer line named "reseau_ep".

In the layer "element_ep" there is a field 'nu_affaire' which contains the business number of each entity.

I want to retrieve this business number (from the field calculator) by creating a 'nu_affaire' field in the 'reseau_ep' layer.

The Qgis #refFunctions # plugin is well suited for this operation, but I want to get the code expression to get there.

Here are the layers and attribute tables.

• I think you would need to bring them into the same layer. Or us a for loop in a python expression to add the value to each field or this is how I would approach it. – Leith Hawkins Jun 25 '17 at 8:15
• Could you explain how these points are related to the lines (1) location-wise: are they sitting directly on top of lines, or touching? (2) record-wise: are they in 1-to-1 relationship or potentially 1-to-N? If there are two or more points on top of one line, the solution would require to select one of them. – Kazuhito Jun 25 '17 at 9:58
• Yes, the lines pass over the points (lines constructed from the points). A line can have several points (1, n) and a point constructed one and only one line (1, 1) ... the combination of the layers is not conceivable because it can have (in most cases) more Entities in the layer of point that in the layers of line. That is why just retrieving the value of the business number in a point layer by a field calculation expression would be formidable (while creating the new field in the line layer) – Narcisse D. Jun 25 '17 at 11:04

Requirement:

• refFunctions plugin to use `geomnearest()` and `dbvalue()`.
• QGIS version: 2.16 or 2.18 to use `concatenate()` expression in aggregate group.

As your `Lines-Points` is in `1-N relationship`, most `Field Calculator` tasks have to be done on `Point` layer.

(1) On your "element_ep" `Point` Layer attributes, create a new text field (let's call it `line`) by:

``````geomnearest('reseau_ep', 'numero')

// This will add name of corresponding (nearest) line for each point.
// 'reseau_ep' is your target layer, which is the Line layer
// 'numero' is your line number in Line layer attributes
// NB. put them in single quotation marks.
``````

(2) Also on the `Point` layer create a new string field (let's say `strings`) by:

``````concatenate("nu_affaire", group_by:="line", concatenator:=', ')

// This will create a concatenated text of nu_affaire, grouped by linenames obtained above.
// Note double quotation marks to wrap fields, and single for ', '.
``````

Then close `Point` layer attribute table and go to "reseau_ep" `Line` layer

(3) Open attribute table of your `Line` layer and start Field Calculator. Create a new text field (shall we call it `outcome`?)

``````dbvalue('element_ep', 'strings', 'line', numero)

// This will lookup your Point layer (element_ep) and extract strings field.
// The condition is `line` = numero; i.e. key=`line` and value=numero
// NB. (again) put them in single quotation marks, except for numero (which is Value).
``````

Final product is your `Line` layer attribute table, which has an `outcome` field in which you find combined text of all the points (nu_affairs) per each line.

• Yes Perfect! After several attempts it worked. Mr. Kazuhito, I am grateful for this solution ... Thank you, Thank you again ... – Narcisse D. Jun 25 '17 at 18:47
• Great, thanks @NarcisseD. I am glad to hear that it worked out. :) – Kazuhito Jun 25 '17 at 21:33
• works perfectly for a similar situation (gis.stackexchange.com/questions/329135/…) - just have to use to_string() on a values that are not text or it fails. – GeorgeC Jul 19 at 1:39