6

I have an excel with 4 columns (Region; District; village; number of cases) Have a shapefile with dots for villages with many columns including (Region ; District ;Village)

I want to plot the cases of the excel (as dots on the villages) by joining the excel file and the shapefile.

The problem is that there are many villages (1000 in total) and many of them have the same name, but belong to different districts. The names of the districts are unique.

Therefore, I would need to join by 2 fields, (not only by village, but , by village and by district). I would need to join excel and shapefile by the fields "District" and " village" so the new column "cases" in created in the shapefile attributes table. I wonder HOW IS THAT DONE?? In QGIS I have learnt to join by 1 field (village), but if I add a second join (district) I lose many of my cases.

Is there a way QGIS can do that? Do I need to make some conditional or something?

4
  • The key here is to make two fields into one. Like lot and plan becomes lotplan (eg. Lot 10 on SP1234 -> 10/SP1234) in BOTH tables.. then join as normal. This is easy in Excel by adding a new header cell and use the function =A1 & "\" & B1 (or whatever you cells are) and then use field calc in QGIS. Commented Jun 19, 2015 at 23:21
  • That is a good idea. The problem is that you have to repeat the process for every single map you do since the selection at the excel (District and village) is done with drop-down menus. and I was rather looking for a long-term solution, to do many maps systematically (with diff data). Thank you very much
    – Elsa
    Commented Jun 20, 2015 at 8:57
  • Actually, it is not that difficult to do it for every single map. Thanks very much for the idea.
    – Elsa
    Commented Jun 20, 2015 at 9:24
  • @MichaelMiles-Stimson please post an answer so this thread can be marked as answered
    – underdark
    Commented Jun 20, 2015 at 9:56

4 Answers 4

7

The key here is to make two fields into one. Like lot and plan becomes lotplan (eg. Lot 10 on SP1234 -> 10/SP1234) in BOTH tables.. then join as normal. This is easy in Excel by adding a new header cell and use the function =A1 & "\" & B1 (or whatever you cells are) and then use field calc in QGIS. – Michael Miles-Stimson

1
  • 1
    Thanks for the credit Elsa.. perhaps you could extend this slightly with your own experience (and a few screen grabs) to take it from an answer to a good answer. Commented Jun 21, 2015 at 21:30
4

Assuming you only want to use QGIS you could create a virtual field for both layers and use it for the join.
Simply select the table -> Open Field Calculator -> Create a new virtual field of unlimited text.
You can combine the fields you want to use into this "join_field" with a simple:

"District"+'_'+"village"

Performing this twice obviously since you'd need that field in your point layer as well.
Using an underscore as a separator for districts and villages that have hyphens or spaces in their names should work for you.

enter image description here

1
  • 1
    This is a great solution. The only thing I'd add is to use concat("District", "village") instead of "District"+'_'+"village". It may not be the case here, but often there are int or float fields you want to join with string fields, and concat() does a nice job of automatically converting all the values to string to make them concatenate without issues.
    – grego
    Commented Sep 30, 2022 at 0:45
3

joinfield

So... this is how I have done it. For large number of entries, it works. I created drop-down menus in excel , then concatenate in excel: concatenate(a1;b1) then concatenate same fields in the shapefile using FIELD CALCULATOR : concat(field1,field2)

Then Join both and that.s it.

For few registries, I think you can enter data manually on a new column (shapefile). Many thanks for the idea, Elsa

1
  • Remember to accept your answer to close off your question (click the faded tick on the left hand side of your answer) :)
    – Joseph
    Commented Jun 23, 2015 at 10:39
0

Another way would be to add a new field with a unique identifier for each village, irrespective of name. EG "1" might be London (England) and "2" might be London (Canada).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.