How do I dynamically switch view name in sql parameter of dimension?

Looker Q&A Looker
1

There is a “rate” dimension in “masterdata” view using “${corp_1.date}” in sql parameter.

explore: corp_1 {
  join: masterdata {
    ...
  }
}

view: corp_1 {
  dimension: date {
    ...
  }
}

view: masterdata {
  dimension: rate {
    type: number
    sql: (CASE
           WHEN ${corp_1.date} < PARSE_DATE('%F', '2021-07-01') THEN 0.1
           ELSE ${TABLE}.rate  
         END) ;;
    value_format_name: percent_0
  }
}

I want to implement “corp_2” explore as well. However, there is a hard-coding in “rate” dimension. So, I used Liquid to implement it.

explore: corp_1 {
  join: masterdata {
    ...
  }
}

explore: corp_2 {
  join: masterdata {
    ...
  }
}

view: corp_1 {
  dimension: date {
    ...
  }
}

view: corp_2 {
  dimension: date {
    ...
  }
}

view: masterdata {
  dimension: rate {
    type: number
    sql: (CASE
           {% if _explore._name == 'corp_1' %}
           WHEN ${corp_1.date} < PARSE_DATE('%F', '2021-07-01') THEN 0.1
           {% elsif _explore._name == 'corp_2' %}
           WHEN ${corp_2.date} < PARSE_DATE('%F', '2021-07-01') THEN 0.2
           {% else %}
           WHEN ...
           {% endif %}
           ELSE ${TABLE}.rate  
         END) ;;
    value_format_name: percent_0
  }
}

But, LookML varidation error occurs:

Inaccessible view “corp_2” referenced in “masterdata.rate”. “corp_2” is not accessible in explore “corp_1”. Check for missing joins in explore “corp_1”.

How can I solve this problem?

1 Like
2

Hi @masutaka ,

This is a great question. We have some general docs on the inaccessible field error here. Generally, we want to make sure wherever masterdata is used corp 2 is joined in such that the reference to corp_2.date is accessible.

Alternatively, we can use a fields parameter to exclude the rate dimension only in the explores where both masterdata and corp 2 are joined in. Another thing to check would be that there are no fields parameters that are excluding corp_2.date in an explore.

Please let us know if you have any questions!

Thanks,

Eric

1 Like
3

Thank you! It worked!

I have solved the problem by referring to “How Can I Fix This Error? > Join the Missing View” of here.

explore: corp_1 {
  join: masterdata {
    ...
  }
}

explore: corp_2 {
  join: masterdata {
    ...
  }

  # Because the above masterdata refers all_media field(s)
  join: corp_1 {
    fields: [date_raw]
    type: left_outer
    relationship: one_to_one
    sql_on: ${corp_2.date_raw} = ${corp_1.date_raw} ;;
  }
}

view: corp_1 {
  dimension: date {
    ...
  }
}

view: corp_2 {
  dimension: date {
    ...
  }
}

view: masterdata {
  dimension: rate {
    type: number
    sql: (CASE
           WHEN ${corp_1.date} < PARSE_DATE('%F', '2021-07-01') THEN 0.1
           ELSE ${TABLE}.rate
         END) ;;
    value_format_name: percent_0
  }
}

If the masterdata becomes more complex, I might create masterdata for corp_1 and corp_2,… using extensions.