Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Filter Issue with Dimension

  • 20 June 2019
  • 5 replies
  • 331 views
Purnaram_Kodavatigan

I am trying to setup filter for a dimension field and use it in explore.


dimension: fiscal_year_quarter {

type: string

sql: ${TABLE}.“FiscalYearQuarter” ;;

suggest_explore: dim_fiscal_date_v1

suggest_dimension: dim_fiscal_date_v1.FiscalYearQuarter

}


But when I use it in explore it comes back saying no Suggestions Available.


Any Help Appreciated.

5 replies

izzy
Userlevel 7
Badge +1

Is there a specific reason that you’d like to suggest from dim_fiscal_date_v1.FiscalYearQuarter and not use the built in suggestions from the ${TABLE}.“FiscalYearQuarter” column?


If you remove suggest_explore and suggest_dimension to show the default filter suggestions, does it show suggestions accurately?

Purnaram_Kodavatigan

Yes, Built in suggestions are not working.


Regards

PK

izzy
Userlevel 7
Badge +1

Huh okay. A great way to troubleshoot suggestions is to check out the Admin > Queries panel to see what the actual suggestion query looks like. It’ll say “Filter Suggestions” and then potentially show an error, or some SQL that might illuminate what the issue is.

Purnaram_Kodavatigan

Receiving following Error.


Java::OrgPostgresqlUtil::PSQLException: ERROR: line 6:39: mismatched input ‘ILIKE’ expecting {, ‘.’, ‘[’, ‘AND’, ‘AT’, ‘BETWEEN’, ‘EXCEPT’, ‘GROUP’, ‘HAVING’, ‘IN’, ‘INTERSECT’, ‘IS’, ‘LIKE’, ‘LIMIT’, ‘NOT’, NRLIKE, ‘OFFSET’, ‘OR’, ‘ORDER’, RLIKE, 'O

izzy
Userlevel 7
Badge +1

Okay, so that indicates there’s a SQL error with the filter suggestion query, preventing it from returning results— Possibly with the derived table definition of dim_fiscal_date_v1 (if that is a derived table, just guessing). The full SQL may be helpful, as well as trying to explore on dim_fiscal_date_v1 and querying for that FiscalYearQuarter to see what the sql issue is.

Reply


Terms of Service