Hi all!
I have two tables: 1) Customer and 2) Customer phone numbers. Each customer may have many numbers but only one is active at a time. In SQL I just do a subquery on the phones effective_from field with a max to find the latest one. Something like this:
SELECT
CUST.CUSTOMER_NAME
CUST_NO.TEL
FROM "DEMO"."CUSTOMER" CUST
left join "DEMO"."CUST_TEL" CUST_NO
WHERE CUST.CUSTOMER_PK = CUST_NO.CUSTOMER_PK
AND CUST_NO.EFFECTIVE_FROM =
(
SELECT MAX(CUST_NO_2.EFFECTIVE_FROM)
FROM "DEMO"."CUST_TEL" CUST_NO_2
WHERE CUST.CUSTOMER_PK = CUST_NO_2.CUSTOMER_PK
)
I do this in a derived table now and it works fine but I wonder how I can do this using LookML?
Any ideas?
I did a max measure in the view for CUST_TEL and now I want to join this view into my CUST view in an explore.
Cheers,
c