LookML model join is using fields as numeric when they are both defiled as string

GenDemo
Participant V

Hi All

I am getting the following error in a LookML model on a join - it seems to be trying to join post codes as numeric values, even though both dimensions are defined as sting in the respective views.

image
image
Any idea why this would be? and how to I get this fixed?

we are using a snowflake back-end.

I have tried to put a ‘to_char()’ conversion in (as below), but this does not seem to translate into the explore window (which is also wierd).

0 6 901
6 REPLIES 6

Hey @GenDemo,

defining the type in LookML won’t help if the column’s type does not correspond.
Are you sure one or more of your underlying columns (address_suburb and/or postcode) are not of numeric types?

GenDemo
Participant V

there are non numeric values in there as the data is quite dirty.
But I dont understand why it is trying to join it as numeric values as opposed to string.

You misunderstood me, the datatype of one of your columns is probably not VARCHAR but maybe a NUMERIC or FLOAT or NUMBER.

You can check that in sql runner by using the “describe” shortcut or run this query:
DESCRIBE TABLE <schema.table_name>

GenDemo
Participant V

ah I see ok.
yes my postcode in dim_geolocations is a numeric.
but why would the to_char() function not show in the SQL generated by the explore?

It definitely should.

Could you try replacing your dimension dim_geolocations.postcode with

dimension: postcode
   type: string
   sql: try_cast(${TABLE}.POSTCODE as varchar)

I’ve used the following for joining tables with different field types :

cast(${d_table.t_source_pk_id} as integer)

Top Labels in this Space
Top Solution Authors