Question

How to Join on distinct dimension

  • 5 August 2019
  • 3 replies
  • 602 views

I want to join two tables on the field which has duplicates in both the table. So, the sample data and query is like-


Table 1:

ID Name Hobby

1 ABC Hockey

1 ABC Cricket

1 ABC TT

2 DEF Swimming

2 DEF TT


Table 2

ID Month AmountPaid

1 Jan 12

1 Feb 16

2 Jan 14

2 Feb 20


SELECT a.ID, a.NAME, sum(AmountPaid) as TotalAmt from TABLE2

JOIN (SELECT DISTINCT ID, NAME from TABLE1) as a

on a.ID = Table2.ID


So here I am thinking to create one more dimension in one table with type as distinct and join that distinct dimension to get the total amount paid.


dimension: id {

type: string/distinct

sql: ${TABLE}.id ;;

}


Please suggest…


3 replies

Hi Ashita,


Assuming your IDs and Name fields always correspond to the same unique combination i.e. one name per ID, then I’d do it this way - this was written in BigQuery so the syntax may vary slightly.


SELECT A.ID,

A.Name,

B.TotalAmtPaid

FROM (

SELECT

ID,

SUM(AmountPaid) AS TotalAmtPaid

FROM

Table 2

GROUP BY

ID) AS B

LEFT JOIN (

SELECT

ID,

Name

FROM

Table 1

GROUP BY

ID,

Name) AS A

ON B.ID = A.ID


This should give you a table with three columns: ID, Name and TotalAmtPaid - from there you can set dimensions as you have above for id. Hope this helps!

Hi Adam, I know how to do in SQL but don’t know in Looker format. Please suggest in Looker BI tool.

Try something like this:


view: table_1 {
derived_table{
sql: SELECT DISTINCT ID,Name FROM Table_1;;
}

dimension: id {
type: string
sql: ${TABLE}.id ;;
primary_key: yes
}

dimension: name {
type: string
sql: ${TABLE}.name ;;
}
}

view: table_2 {
sql_table_name: Table_2;;

dimension: pk {
type: string
sql: CONCAT(${TABLE}.id,${TABLE}.Month) ;;
primary_key: yes
}

dimension: id {
type: string
sql: ${TABLE}.id ;;
primary_key: yes
}

dimension: amount_paid {
type: number
sql: ${TABLE}.amountpaid ;;
}

measure: total_amount_paid {
type: sum_distinct
sql: ${amount_paid};;
sql_distinct_key: ${pk};;
}
}

explore: test_join {
view_name: table_2

fields: [ALL_FIELDS*]

join: table_1 {
type: left_outer
relationship: many_to_one
sql_on: ${table_2.id} = ${table_1.id};;
}
}

Reply