User Guide

Getting Started

Help Center

Documentation

Community

Training

LookML Measure

done
low_priority
reply
(cristian) #1

Hi all!
Im a beginner so bare with me…
I have some strange data that I want to create some measures on. I have a transactions table where each transaction must be one of two - there must always be a pair where a dimension is differentiating the transaction. In my example below Person may only be Z or A. All other dimensions must be exactly the same. So for date 2018-12-01 we see that the product 1234 was received by station Y and checked by person Z. But it must also be checked by person A.

First measure should be the amount of products received that dont have been checked by both Z and A. (2 below)
Second measure should be the amnount of products received that have only been checked by Z. (1 below)
Third measure should be the amnount of products received that have only been checked by A. (1 below)

Date, Product, Station, Person
2018-12-01, 1234, Y, Z
2018-12-01, 1234, Y, A

2018-12-02, 2345, Y, Z
2018-12-02, 2345, Y, A

2018-12-03, 3456, Y, Z

2018-12-04, 3457, Y, A

Any ideas or suggestions that point me in the right direction?
In sql I group the data on concat(Date,Product,Station) and do a count. I use the rows with count = 1 and left join the same table.
How can I accomplish this in LookML?
Br
Cris

(Izzy) #2

My first instinct with something like this, where a self-join is the obvious way to solve the problem, is usually to use a derived table. That’s kind of a cop-out though, so I’m thinking about a way to do this in pure LookML.

Just making sure you knew that making a SQL based derived table was also an option to get you where you need to be! more soon…

(cristian) #3

I solved it with a derived table. My sql is a self-join but I wanted to know if there’s a way of solving this with LookML.

(Izzy) #4

So there is a way to do this in just LookML, basically by simulating the same derived table logic. Instead of doing a self join in a derived table, we’ll do a self join in LookML— Using a join with the from: parameter.

Ex:

explore: table1 {
     join: table1selfjoin {
           from: table1           
           type: full_outer
           sql: join conditions here ;;
     }
}

Then, you can make some dimensions + measures that return the counts you’re looking for.

  1. A few dimensions that coalesce the date, product, and station fields from both tables, so that all rows have values for date (regardless of which side of the join they came from)

  2. Dimensions that use the person column from each table to determine who the transaction was seen by, or a status dimension that returns “missing a” “missing z” “has both” or “neither”.

  3. Now you can use filtered measures to get a count of rows where where table1.name <> ‘a’ and table1selfjoin,name <> ‘a’. Or, if you used the status dimension, just filter on that.

Does that make sense? All credit to @zlebowitz here.

(cristian) #5

I think it makes sense. I’ll try this. Thanks!