I apologize if someone else has asked this question but I can’t seem to find the answer I’m looking for. I’m trying to figure out the specific steps to creating a new view to isolate some mixed data from an existing view.
Here’s the scenario:
I have a view with data on users including first_name, last_name, address, etc. The view has a mixture of user types in it. For the example’s sake there’s an additional dimension called user_type with numeric values and each one of these values represents a different user type. 1 = Reader and 2 = Payer. So if you can imagine a digital publication being sent out to a group of people all under the same Subscription ID; like a family. The person who pays the subscription bill has a user_type of 2 and the ones who get the publication on that same subscription are user_type 1.
For outputs, the analysts like to output a table with column names like *Payer First Name, Payer Last Name, Reader First Name, Reader Last Name* for each record. In SQL we’d do something like
SELECT s.*
, r.first_name AS ReaderFirstName
, r.last_name AS ReaderLastName
, p.first_name AS PayerFirstName
, p.last_name as PayerLastName
FROM subscriptions s
LEFT JOIN users r ON s.subscription_id = r.subscription_id AND r.user_type = 1
LEFT JOIN users p ON s.subscription_id = p.subscription_id AND p.user_type = 2
For the life of me I can’t really figure out how to create views from this. In my head, each table reference in this SQL Query above needs to be it’s own separate view on Looker. What I can’t seem to figure out is how to filter on user_type at the view level.
I’ve got a view called users and I’ve started to create a new view called payerusers that extends: [users] but that’s as far as I’ve gotten Google searching the specifics of how to make this work. The view payerusers should only output those users who’s user_type = 1. How do I express that?
