Question

How to use join to PDTs in explore definition?

  • 6 August 2015
  • 2 replies
  • 84 views

I’m trying to perform an INNER JOIN to a PDT as part of a join in an explore. I’ve previously used SQL_TABLE_NAME in defining PDTs that depend on other PDTs, but that syntax doesn’t appear to work when defining an explore. Perhaps I’m missing something obvious about how to do this correctly?


Incidentally, in the very same explore, I also had to join to a regular (non persistent) derived table, which works just fine.


- explore: repurchase_performance_by_customer
joins:
- join: user_purchase_facts # regular, non-persistent derived table - this works
sql: INNER JOIN looker_scratch.user_purchase_facts as user_purchase_facts ON user_purchase_facts.user_id = repurchase_performance_by_customer.user_id

- join: user_acquisition_product_facts # PDT - this one doesn't work
sql: INNER JOIN ${user_acquisition_product_facts.SQL_TABLE_NAME} ON user_acquisition_product_facts.user_id = repurchase_performance_by_customer.user_id

Error:

Unknown or inaccessible field “user_acquisition_product_facts.SQL_TABLE_NAME”


2 replies

Userlevel 4

Have you tried using sql_on: syntax? sql: has been deprecated as of release 3.12 (see docs)


- join: user_acquisition_product_facts 
type: inner
sql_on: ${user_acquisition_product_facts.user_id} = ${repurchase_performance_by_customer.user_id}

Since user_acquisition_product_facts is the view with a PDT, this should locate it within looker_scratch without needing any SQL_TABLE_NAME syntax.

That did the trick. Thanks!

Reply