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?