Filter Issue with Dimension

  • 20 June 2019
  • 5 replies

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

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?

Yes, Built in suggestions are not working.



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.

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

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.