Using a field from one view as a display filter but using another field to filter

[RFoster]

Hi,

The issue is we have a view called t_trade_product that contains the following:

dimension: product_code {
type: string
label: "Product Code"
sql: ${TABLE}.prod_cd;;
}

dimension: product_type {
type: string
label: "Product Type"
sql: ${TABLE}.prod_typ;;
}

dimension: product_long_name {
type: string
label: "Product Long Name"
sql: ${TABLE}.long_name;;
}

dimension: bb_code {
type: string
label: "BB Code"
sql: ${TABLE}.bloomberg_code;;
}

dimension: exch_id {
type: string
label: "Exch ID"
sql: ${TABLE}.exch_mrkt_id ;;
value_format_name: id
}

dimension: product_name {
type: string
label: "BB Code - Product Long Name"
sql: concat(${bloomberg_code}, " - ", ${product_long_name});;
}

I want to create on a filter on a dashboard to use product_name from the t_trade_product view that will then filter another view by the product code

t_trade_event view

dimension: product_code {
type: string
label: "Product Code"
sql: ${TABLE}.prod_cd;;
}

dimension: product_type {
type: string
label: "Product Type"
sql: ${TABLE}.prod_typ;;
}

dimension: exch_id {
type: string
label: "Exch ID"
sql: ${TABLE}.exch_mrkt_id ;;
value_format_name: id
}

the two views are joined in the explore as follows:

# join: product {
# view_label: "Product Information"
# type: inner
# relationship: one_to_one
# sql_on: ${t_trade_product.product_code} = ${t_trade_event.product_code}
# and ${t_trade_product.exch_id} = ${t_trade_event.exch_id}
# and ${t_trade_product.product_type} = ${t_trade_event.product_type} ;;
# }


So essentially the user will see the product long name and select it in the dashbaord but the sql will filter on the product code from the event view.
reason being the bb code and the product code are differnet and some users don't know what the product code is hence wanting to display a concat of bb code
and long name in the product view for the filter in the dashbaord.


Is this possible?