Alternate Views for Mixed Data

[jsimmons]

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?

[IanT]

Create 1 view based on user.

in the view declare (eg.first name) once and set it to hidden. Create your 2 versions of this dimension (payer/reader) based on this hidden dim but add your custom label and description.

In the model you will join in your users view twice (using the “from” or “viewname” attribute in the join - subtle difference between the two I can’t remember off top of my head). On the join condition set your user type. Use the fields attribute on the join the only bring through the relevant fields for that user type.

[IanT]

Or extend it as you mentioned to overwrite the field labels etc but again on the join declaration you would call the extended view rather than the base on ….and out the user type filter on the sql on.

[jsimmons]

Thanks for the response IanT. I’m new to Looker and getting the swing of it. On another recommendation I ended up going with a derived table. Doing it that way helped me learn about derived tables  but also let me meet my goal with fewer lines of code. Thanks for the help.