Solved

Derived table from join of three tables

  • 16 February 2021
  • 2 replies
  • 33 views

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 ;;
    }
}

 

 

icon

Best answer by IanT 16 February 2021, 22:20

use “TABLE”. eg. ${TABLE}.matchstatus.

View original

2 replies

Userlevel 6
Badge

use “TABLE”. eg. ${TABLE}.matchstatus.

Thank you, I guess I was putting too much thought into the wrong place! I made the fix and everything is working.

Reply