Question

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

  • 25 June 2020
  • 6 replies
  • 226 views

Userlevel 3

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).



6 replies

Userlevel 5
Badge

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?

Userlevel 3

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.

Userlevel 5
Badge

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>

Userlevel 3

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?

Userlevel 5
Badge

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)

Reply