Prepopulate linked filter

[sergey_b]

Dear GCCommunity,

I have an issue, tried to solve myself or find on the web -> unfortunately without a result.

Here is the problem:
I have 2 tables, see template below:
Citizen (Id) - Associated Zip code ; 
 Zip code  - City;
 
On the looker dashboard i link those 2 tables and add 2 filters on Zip and on City.
The second filter on City is linked to Zip, and i know that it is always a unique value per Zip.
I would like to filter my table on the City filter, Zip filter is not connected to the Table.

Potentially i see 2 solutions:
1. Autopopulate City filter based on the chosen Zip code.
2. Add a filter in the definition of the views/explore, perhaps via using parameters, to query first second Table (zip-city) and get as response the associated City.

Could you please advise me?

################################
Here would be BQ data:

CREATE OR REPLACE TABLE `XXX.citizen-city`  

AS

  SELECT 1  id, '1001' zip UNION ALL

  SELECT 2  id, '1001' zip UNION ALL

  SELECT 3  id, '1002' zip UNION ALL

  SELECT 4  id, '1003' zip UNION ALL

  SELECT 5  id, '2100' zip UNION ALL

  SELECT 6  id, '2200' zip UNION ALL

  SELECT 7  id, '2200' zip UNION ALL

  SELECT 8  id, '2300' zip UNION ALL

  SELECT 9  id, '3010' zip UNION ALL

  SELECT 10 id, '3010' zip UNION ALL

  SELECT 11 id, '3020' zip UNION ALL

  SELECT 12 id, '3020' zip UNION ALL

  SELECT 13 id, '4000' zip

;

CREATE OR REPLACE TABLE `XXX.zip-city`  

AS

SELECT '1001' zip, 'London' city, UNION ALL

SELECT '1002' zip, 'London' city, UNION ALL

SELECT '1003' zip, 'London' city, UNION ALL

SELECT '2100' zip, 'Paris' city, UNION ALL

SELECT '2200' zip, 'Paris' city, UNION ALL

SELECT '2300' zip, 'Paris' city, UNION ALL

SELECT '3010' zip, 'Berlin' city, UNION ALL

SELECT '3020' zip, 'Berlin' city, UNION ALL

SELECT '4000' zip, 'Bern' city

;
################################

Here comes the definition of views/explore in the Looker
######## Views########
view: citizen_zip {

sql_table_name: `XXX.citizen-city`;;

dimension: id {
type: number
description: "Citizen ID"
sql: ${TABLE}.id;;
}

dimension: zip {
type: string
description: "Zip code, citizen belongs to"
sql: ${TABLE}.zip;;
}
}

view: zip_city {
sql_table_name: `XXX.zip-city`;;

dimension: zip {
type: string
description: "Zip code of part of a city"
sql: ${TABLE}.zip;;
}

dimension: city {
type: string
description: "City, zip belongs to"
sql: ${TABLE}.city;;
}
}

######## Explore########
explore: test_citizen_zip_city {

from: citizen_zip
label: "Test View"

join: zip_city {
relationship:many_to_one
sql_on: ${test_citizen_zip_city.zip} = ${zip_city.zip} ;;
}
}