String to number in Look ML

[Sophie1]

Hey there! 

A google sheet I imported unfortunately shows all my values as strings instead of numbers in Looker. For this reason I’d like to add a parameter in LookML to ensure that all strings from my doc are converted into strings, so I don’t have to add it as a custom calculation every time - is this possible? 

[kevin_dunn]

Hey Sophie,

The sql parameter for individual fields in Looker will typically accept any SQL expression that’s compatible with your particular flavor of SQL (Redshift, MySQL, PostgreSQL, etc.). With that in mind, you should be able to cast this field as a numeric datatype inside of the sql parameter for the field. For example if you have:

dimension: string_dim {
    type: string
    sql: ${TABLE}.string_field;;
}

We can convert this to a numeric field using a cast function (exact syntax will vary by dialect, but most are pretty similar):

dimension: string_to_num_dim {
    type: number
    sql: CAST(${TABLE}.string_field as INTEGER);;
}

This will then be treated as an integer when we export the data for other uses, refer to this field elsewhere, etc.

Hope this helps!

-KD