Creating a PDT on the fly?

[dburney]

I recently discovered a PDT can be created on the fly either using a WITH statement or using Liquid. Right now I am trying to create a PDT I can join to my native Explore without having to create a schema for my DB. Can someone give me more details or docs on how to implement? Thanks

[moebe]

Hi
No, you can write a PDT (whether persistent or not) with liquid.
But you can use liquid.

Here are a few examples:

This one will create a pdt without persistence.

So looker will write the sql like:
 

view: example1{
  view_label:"Not persistent”
  derived_table: {
    sql:
    SELECT    current_date() from dual
  }

with example1 as (select current_date()  as date from dual)

select blub from bla left outer join example1 on (bla.date = example1.date)

if you add persistance by using

persist for, or datagroup_trigger syantax

view: example2{
  view_label:"persistent”
  derived_table: {

datagroup_trigger:mytrigger
    sql:
    SELECT    current_date() from dual
  }

the above sql will look like:

select blub from bla left outer join yourPdt_schema.cryptic_looker_name_example1 on (bla.date = example1.date)

And in this sql you can use liquid (in both versions)

f.e.

view: example1{
  view_label:"Not persistent with liquid”
  derived_table: {
    sql:
    SELECT   {% if fview.field_name._is_selected %}   current_date() {% else %} current_date()-14 from dual
  }

So looker will change the sql based on a user selection to current_date() or current_date()-14

hope that helps?

Best

Ben

[dburney]

Thanks Ben, I am going to examine further. I was able to resolve my issue through this article: https://help.looker.com/hc/en-us/articles/360023742593-Identifying-and-Building-PDTs-for-Performance...

My goal was to create a PDT I could join to an Explore and ended up creating a temp schema in the config because it wouldn’t save without.