0

Need to transform this query to LookML

SELECT Accounts_Unlock_Price, 
       Accounts_Upfront_Price,
       Portfolio_Derived_Previous_Cumulative_Paid,
       Portfolio_Derived_Previous_Cumulative_Paid/(Accounts_Unlock_Price - Accounts_Upfront_Price) * 100 AS FRR
FROM Accounts, 
     Portfolio_derived_20
WHERE Accounts.Accounts_Angaza_ID = Portfolio_derived_20.Portfolio_Derived_Account_Angaza_ID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
WNT01
  • 3
  • 3

1 Answers1

2

The structure of this in LookML will depend on your model. SQL isn't really convertable to LookML, as LookML generates SQL, as opposed to just translating it.

LookML uses view files to describe tables, and you've got two tables here, so you'll need two view files. They might look something like this, though I'm just guessing:

view: accounts {
  sql_table_name: Accounts ;;

  dimension: Accounts_Unlock_Price {
    type: number
    sql: ${TABLE}.Accounts_Unlock_Price ;;
  }

  dimension: Accounts_Upfront_Price {
    type: number
    sql: ${TABLE}.Accounts_Upfront_Price ;;
  }

  dimension: Portfolio_Derived_Previous_Cumulative_Paid {
    type: number
    sql: ${TABLE}.Portfolio_Derived_Previous_Cumulative_Paid ;;
  }

  dimension: FRR {
    type: number
    sql: ${Portfolio_Derived_Previous_Cumulative_Paid}/(${Accounts_Unlock_Price} - ${Accounts_Upfront_Price}) * 100;;
  }

  dimension: Angaza_ID {
    type: number
    sql: ${TABLE}.Accounts_Angaza_ID ;;
  }
}

view: Portfolio_derived {
  sql_table_name: Portfolio_derived_20 ;;
  ##don't know what's in this file

  dimension: Account_Angaza_ID {
    type: number
    sql: ${TABLE}.Portfolio_Derived_Account_Angaza_ID ;;
  }
}

Once you've defined the fields in views, you need to join them in an explore, so you can actually query them.

I'm again just guessing, and it looks like you're doing a CROSS join here but I'm not sure.

explore: accounts_angaza {
  view_name: accounts
  sql_always_where: ${Portfolio_derived.Account_Angaza_ID} = ${accounts.Angaza_ID} ;;

  join: Portfolio_derived {
    type: cross
  }
}

This would let you open that explore in the Explore UI and visually select Unlock price, upfront price, cumulative paid, and FRR, and query that. That's the easy part, "building the query". The harder part is laying the framework for it, which is what I've described a bit above.

This might be a helpful resource if you're comparing SQL to Looker queries, as it explains how Looker generates SQL: https://docs.looker.com/data-modeling/learning-lookml/how-looker-generates-sql

This explains how joins work and has links out to the lower-level components you have to build before you can join stuff. https://docs.looker.com/data-modeling/learning-lookml/working-with-joins

Good luck! If you've got more questions, there's a whole lotta Lookers that hang out at https://discourse.looker.com so that could be a good resource for future Looker questions too.

Izzy Miller
  • 200
  • 6