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
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
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.