Question

Drilldown on derived dimension

  • 30 March 2021
  • 0 replies
  • 23 views

I am using Standard BQ SQL and trying to create a hierarchy on a dimension

  dimension: category {
    type: string
    sql: ${TABLE}.category ;;
  }

that I split into three columns

 

  dimension: A_category {
    description: "Top level category"
    type: string
    sql:SPLIT(${category}, "/")[SAFE_OFFSET(1)];;
  }

  dimension: B_category {
    description: "Top level sub-category"
    type: string
    sql:SPLIT(${category}, "/")[SAFE_OFFSET(2)];;
  }

  dimension: C_category {
    description: "Bottom level sub-category"
    type: string
    sql:SPLIT(${category}, "/")[SAFE_OFFSET(2)]
    ;;
  }

 

I want B_category to be a drill down for A_category, and C_category to be a drill down for B_category.

However, the following gives me an error:

  dimension: A_category {

    drill_fields:[B_category,C_category]
    description: "Top level category"
    type: string
    sql:SPLIT(${category}, "/")[SAFE_OFFSET(1)];;
  }

 

Do I need to nest the SQL for creating the other categories inside the drill_fields function for A_category?

 

Thanks


0 replies

Be the first to reply!

Reply