Liquid: Conditional JOIN to explore based on value in another field

[thomasadams]

I am trying to join two view files onto one explore based off another views value, the two view files are from different data sources. The one view I am trying to connect labels which source each rows come from under the dimension ${merged_opportunity_line_item.system_source}. If ${merged_opportunity_line_item.system_source} == “Salesforce” I want to join to the explore on a different condition else join on a different condition. Currently this is the code I have.

This first view is an extended view file from a base view file in another folder structure. Called

sf_country_c

join: merged_sf_country_c {
        view_label: "CRM Country"
        sql_on: {% if merged_opportunity_line_item.system_source == "Salesforce" %}
                  ${merged_sf_country_c.id} = ${merged_opportunity_line_item.country_c}
                {% else %}
                  ${merged_sf_country_c.pega_country_id_c} = ${merged_pega_country.countryid}
                {% endif %} ;;
              relationship: many_to_one
      }

This second view is an extended view file from a base view file in another folder structure. Called

country

  join: merged_pega_country {
    view_label: "CRM Country"
    sql_on: {% if merged_opportunity_line_item.system_source == "Salesforce" %}
              ${merged_sf_country_c.id} = ${merged_opportunity_line_item.country_c}
            {% else %}
              ${merged_sf_country_c.pega_country_id_c} = ${merged_pega_country.countryid}
            {% endif %} ;;
          relationship: many_to_one
  }

This might be a better representation of what I am trying to achieve.

join: merged_sf_country_c {
    view_label: "CRM Country"
    sql_on: {% if merged_opportunity_line_item.system_source == "Salesforce" %}
              ${merged_sf_country_c.id} = ${merged_opportunity_line_item.country_c}
            {% else %}
              ${merged_sf_country_c.pega_country_id_c} = ${merged_opportunity_line_item.country_c}
            {% endif %} ;;
          relationship: many_to_one
  }

Currently I am not getting any data for the “CRM COUNTRY” fields. 😦

Any help is much appreciated. Still learning all the intrinsics of liquid and plan on mastering it’s dynamic ability. Thank you in advance.