0

I just started to work with QGIS recently and am struggling with the following:

  • I have 1 a CSV table on offshore branches of certain institutions including info on their respective countries of origin. No geographical data except the columns with country names (Home Country, Host Country).

    Here is an example of the table with the relevant columns:

Institution name Institution Branch Home Country Host Country
xxx xxx2 Germany Saudi Arabia
yyy yyy2 Germany India
zzz zzz2 USA India
  • I also have 2 a polygon layer with the world countries in which each country takes 1 row, and each with it's own polygon represented in the shapefile (the country name column is named name)

Both attribute tables use the same format for the country names.

Now I would like to

  1. add a field (column) to the 2 country layer's attribute table that counts for each country how many institutions have it as a Home Country in the CSV table. Using the above table as an example, the new column would count 2 for Germany, and 1 for USA.
  2. add additional columns to the 2 country layer's attribute table that count for each country how many institutions have it as a Home Country in the CSV table AND have a branch in a certain Host Country. For example in the above table I would like to only count for those countries with institutions with a branch in Host Country = India. The resulting column should count 1 for Germany, and 1 for USA.

1 Answer 1

2

One step for me :

  • Select the country layer 2, open field calculator, name your new field of type Integer and compute it with the expression :
coalesce( aggregate( layer:= 'offshore_institutions_layer_name', aggregate:= 'sum', expression:= 1, filter:= "Home Country" = attribute(@parent, 'name') ) , 0) 

Explanation :

This expression aggregate the 1 offshore institutions with the same "Home Country" name as the current country layer 2 feature "Home Country" value, and sum the value 1 for each (it's the same as count in this case).

For the second part, you can add a condition to your filter :

filter:= "Home Country" = attribute(@parent, 'name') AND "Host Country" = 'India' 
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.