QGIS 3.2.3: I have a shapefile and an Excel table which is joined to the shapefile based on matching IDs.

I would like to filter the shapefile while using a joined field from the Excel table.

When I select "Filter" at the shapefile, I get the message that filter on joined tables is only possible when creating a "Virtual Layer". I click "Yes" when asked if I want to create a virtual layer.

Nevertheless, when the query builder appears, the joined fields do not appear. Did the creation of the "virtual layer" not work?

Is this not possible or am I doing something wrong?

I have found this older article: is this still valid that it is not possible in QGIS? How do you filter a layer on a joined table in QGIS?

This works in ArcGIS using a "definition query" on a joined layer.

  • any feedback? how do you deal with this issue? – GIS7 Nov 5 '18 at 13:57
  • Not a full answer but this might help: qgistutorials.com/en/docs/performing_table_joins.html – Alex Hajnal Nov 7 '18 at 13:20
  • Thank you. This describes to process of the join which is working. It does not give information about the issue of filtering when using a joined field in the query. – GIS7 Nov 7 '18 at 13:27
  • 2
    Have you defined your joins both in Settings → Project Properties → Relations and in Layer → Properties → Joins? Make sure you've added all the foreign (i.e. excel) columns you want to use in both places. You might also need to set the Prefix in the latter if you have any field name collisions. (Yes, QGIS joins are a PITA) Since you're working with dynamic data also make sure Memory cache is disabled in the latter. – Alex Hajnal Nov 7 '18 at 14:07
  • 1
    Yes, the joined fields show up for me in the filter dialog. Note that I'm using Postgres as my backend not shapefile+excel. What I did encounter was the geometry column of the filtered table not displaying on the map. Yes, joins are a pain in QGIS. If you find a solution that works could you write an answer to your own question; I'm sure a lot of people would find it helpful. – Alex Hajnal Nov 7 '18 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.