Question

Is it possible for an Explore to join a view which includes a liquid variable to itself?

  • 23 January 2023
  • 0 replies
  • 16 views

I have a user_history table with roughly 100 fields, in which each record has a start_date and end_date. I’d like to define one Explore which sits on top of the “raw” view, as well as a second Explore which allows the user to easily compare snapshot values for various user fields at two different times. I’ve defined the view as shown:

view: user_history {
  parameter: snapshot_date {
    type: date
  }

  derived_table: {
    sql:
      SELECT *
      FROM user_history
      WHERE ( {% parameter snapshot_date %} IS NULL
      OR {% parameter snapshot_date %} BETWEEN start_date AND end_date );;
  }

...and I have an Explore built on the view, which works as expected. I then built the snapshot Explore like so:

explore: user_history_snapshots {
view_name: user_history1
from: user_history
view_label: " User History Date 1"
join: user_history2 {
type: full_outer
from: user_history
view_label: "User History Date 2"
sql_on: ${user_history1.user_uid} = ${user_history2.user_uid} ;;
}
}

...but when I run it, I get the error

A LookML model issue prevented this query from running.Variable not found "snapshot_date". Note that liquid in the "sql" of a "derived_table" must reference fully scoped field names (i.e. "join_name.field_name") when the view is joined into an explore using "from".

I’m not quite sure what’s happening behind the scenes - I’ve tried changing 

{% parameter snapshot_date %}

to {% parameter user_history.snapshot_date %}

, which then works if my Explore starts with a different view and then joins user_history to it. But it does not work with the snapshot Explore I have above when I’m joining user_history to itself - there’s no error, just no results and the SQL isn’t what I’m looking for. Basically, I want the underlying SQL to be something like this:

WITH user_history1 AS (
SELECT *
FROM user_history
WHERE ( $snapshot_date1 IS NULL
OR $snapshot_date1 BETWEEN start_date AND end_date )
) ,
user_history2 AS (
SELECT *
FROM user_history
WHERE ( $snapshot_date2 IS NULL
OR $snapshot_date2 BETWEEN start_date AND end_date )
)
SELECT
*
FROM user_history1
FULL OUTER JOIN user_history2 ON (user_history1.user_uid) = (user_history2.user_uid)
LIMIT 500;

Is there any way to do this? Thanks in advance.


0 replies

Be the first to reply!

Reply