FLOAT can't be used as dimension in pivot tables (BigQuery)

[vfx1]

I came across the need to show a floating point coefficient in the rows and then use it in a calculation to generate a measure. Due to a known limitation of BigQuery - floats can’t be used in PARTITION BY - Looker shows an SQL error. Consider the following view:

# Use with Google BigQuery Dielact
view: float_dimension {
  derived_table: {
    # The following SQL generates 100 random floats and splits them in 2 groups by 'chance'
    sql: SELECT RAND() as fl, RAND() > 1/ACOS(-1) as chance FROM unnest(generate_array(1, 100, 1)) ;;
  }
  dimension: fl {
    description: "use as row dimension. It will break as soon as you place 'chance' as pivot"
    type: number
    sql: ${TABLE}.fl ;;
  }
  dimension: chance {
    description: "use as pivot dimension"
    type: yesno
    sql: ${TABLE}.chance ;;
  }

  measure: count {
    description: "put as a measure just for fun :-)"
    type: count
  }
}

Now create an explore and put 'fl' in the rows, 'chance' in the columns. You will get: invalidQuery: Partitioning by expressions of type FLOAT64 is not allowed.

It’s not so very difficult to go around the error. Change the 'fl' to STRING and use CAST AS FLOAT64 every time you need to use it an a calculation. Saying that, it seems more natural that Looker generates SQL which casts to string within the PARTITION BY statement.

Or maybe something smarter…

Thanks