Hi,
I’m very new to Looker and am having difficulty locating documentation on creating a derived table that joins three tables. Database is Snowflake. Here’s my code, but I’m getting errors on my dimensions which specify the table names in the sql.
view: wtg_courtcase_subset {
derived_table: {
sql: SELECT T2.caseid, T2.casenumber,T2.dispositiondate, SPLIT_PART(T2.dispositiondate,‘-’,1) AS dispositiondateyear, T3.da AS districtattorney, T3.county,T3.judicialdistrict, T1.matchid,T1.matchstatus
FROM wtg_1stdistmatch T1
INNER JOIN court_case T2 ON T1.matchid = T2.matchid
INNER JOIN court_location T3 ON T2.courtnumber = T3.courtnumber ;;
}
dimension: caseid {
type: number
sql: ${court_case}.caseid ;;
}
dimension: casenumber {
type: string
sql: ${court_case}.casenumber ;;
}
dimension_group: dispositiondate {
type: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${court_case}.dispositiondate ;;
}
dimension: dispositiondateyear {
type: number
sql: ${court_case}.dispositiondateyear ;;
}
dimension: districtattorney {
type: number
sql: ${court_location}.districtattorney ;;
}
dimension: county {
type: string
sql: ${court_location}.county ;;
}
}
Any help is greatly appreciated!
Shari
dimension: judicialdistrict {
type: number
sql: ${court_location}.judicialdistrict ;;
}
dimension: matchid {
type: number
sql: ${wtg_1stdistmatch}.matchid ;;
}
dimension: matchstatus {
type: string
sql: ${wtg_1stdistmatch}.matchstatus ;;
}
}