Solved

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

  • 28 July 2021
  • 2 replies
  • 67 views

Userlevel 2

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?

icon

Best answer by Eric_Lyons 28 July 2021, 15:23

View original

This topic has been closed for comments

2 replies

Userlevel 4
Badge

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

Userlevel 2

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.