QGIS – Using Aggregate Function for One-to-Many Joins Effectively

aggregateattribute-joinsqgis

I am using QGIS, version 3.24.0. I have a shapefile called "Map_CorrectionDEF", uniquely identify by fields "GID" and "unitcode". It is a grid cell, in which the field "GID" is the cell and "unitcode" the country to which the cell belongs.

On the other hand, I have a CSV file, called crop_main (no geometry), uniquely identify by "unitcode" and "cropclass", where "unitcode" refers to the country and "cropclass" the type of crop.

I present an example of my CSV file:

enter image description here

I would like to added the CSV column to my grid table. Specifically, I would like to assign to each cell the type of crop which exist in the country which the cell belongs. Then, for each cell, which belongs to a country ("unitcode" field), I would like to join the columns of my csv file which has the same value of "unitcode". The goal is to obtain for all the cells of my shape file the information of all cropland which grows in the country. Hence, all the cells of the same country should have the same number of crops.

I think I am doing a one to many because for each cell, I would like to get many crop. Nevertheless, I have many cells for each country, so I think it should be a many to many. I am trying to do this solution discussed here 1:n relations in label expressions & general syntax using aggregate function. However, I am not able to get what I want.

I attach a screenshot of what I am doing. It seems everything is ok but when I press ok, nothing happens. Any ideas of what can be happening?

enter image description here

[Edit: I have still some problems once I apply the answer of @J.R, I attach some screenshot of what I am doing]

Relation:

enter image description here

Expression:

enter image description here

No results:

enter image description here

Best Answer

For the solution in the referenced question to work you need to define the relationship between your two layer before attempting to use the expression.

Your first steep is to go to Project > Properties... then to select the Relation tab. Here you will add a relation with the "Map_CorrectionDEF" layer as parent layer and the "crop_main" as the child, set the "unitcode" field for both the parent and child layer.

Then you should be able to use the expression :

aggregate( layer:= 'crop_main',
            aggregate:='concatenate', 
            expression:="cropclass",    
            concatenator:=', ',
            filter:=attribute(  $currentfeature ,'unitcode')=attribute(  @parent ,'unitcode'))

The result would be for each cell of the grid layer a list of all cropclass from the crop_main table that share the same unitcode. This should work with both text and numeric field (it does work with my 3.22.1 QGIS version) and you should not replace the @parent with with the parent layer name (the @parent is in reference to the relation you set as a first-steep).

Take care to properly define the field where the result should go as text with enough length to accommodate all the value you expect to aggregate (including the concatenator sting)

Related Question