Question

How to avoid trunc date in explore joins


Hi,

When exploring multiple columns views are joined based on the joins defined in model. But I am running into issues when join happening on the date column. Looker by default using trunc( date ), which is causing duplicates when there are multiple entries on same day.

Eg : Instead of     A.date = B.date   its becoming Trunc(A.date) = Trunc(B.date)


3 replies

Userlevel 7
Badge +1

What is the datatype paramater of your dimensions that you’re joining on? Make sure you have datatype:date

Thanks for response.

Actually after the post I tried using type:date_time and it helped resolving the issue.

Userlevel 4

An alternate to Dawid’s great comment; you can use the `_raw` timeframe of a dimension group to avoid any and all date/time SQL interpolation on the fields in question. 

For example, with two fields that look something like this:

--- in both view a and view b

dimension_group: my_dates {
sql: ${TABLE}.my_date_field ;;
...
}

--- in my_model.lkml

explore: my_explore:
from: view_a
...
join: view_b {
sql_on: ${view_a.my_dates_raw} = ${view_b.my_dates_raw} ;;

Would generate SQL without any of the date/time conversion SQL. You can use the _raw timeframe elsewhere in LookML as well anytime you want to avoid extra conversion (for example, double timezone conversion). 

Reply