Hi All!
I have a number in my bq-table that is stored as a string with this format “123.00-” or “123.00+”.
I need to remove the last character and cast this to a float64. My solution works fine but I don’t know if Im doing it the “looker” way. This is my solution:
measure: my_measure {
type: sum
value_format_name: decimal_2
sql:
CAST(
CASE
WHEN substr(${TABLE}.string_field,-1)="+" THEN regexp_replace(${TABLE}.string_field, '[^0-9.]', '')
WHEN substr(${TABLE}.string_field,-1)="-" THEN CONCAT("-",regexp_replace(${TABLE}.string_field, '[^0-9.]', ''))
END
AS FLOAT64)
;;
}
Now, my question is if there’s another way of doing this with Looker functions? Any suggestions?
Cheers,
Cris
Nope, this looks totally on the up and up to me. We have Looker expressions for use in table calculations (and there are to_string/to_number functions), but as far as a measure is concerned— Doing it in the SQL is the way to go.
ok thanks!