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