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)
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.
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).
User | Count |
---|---|
4 | |
1 | |
1 | |
1 | |
1 |