Question

Having issues to pass filter on partition filter as argument to derived sql in LookML

  • 30 August 2021
  • 0 replies
  • 22 views

Hi,

I have a hive table with datestamp as partition in 'YYYYMMDD' format. I am trying to design a view with a derived sql. 
User should be able to filter by Year (e.g. 2020,2021,2022 .. ) and month in mmm (e.g. Jan, Feb etc) in the explore and be able to generate a report 
Based on the above selection the derived SQL 's start_date (datestamp >= ''YYYYMMDD') and end_date (datestamp <= 'YYYYMMDD') should be substituted.

I am tryin the following:

view: my_facts {

derived_table: {
sql:
Select datestamp,dim1,dim2, sum(measure1) as measure1
from myDB.table1 where is_fraud = 0 AND
{% condition start_date_filter %} table1.datestamp {% endcondition %} AND
{% condition end_date_filter %} table1.datestamp {% endcondition %} AND
{% condition dim1 %} table1.dim1 {% endcondition %}
group by datestamp,dim1,dim2
;;
}

filter: dim1 {
type: string
}

filter: start_date_filter {
type: string
suggest_dimension: start_date
}

filter: end_date_filter {
type: string
suggest_dimension: end_date
}


parameter: year_to_select{
type: string
allowed_value: {value: "2021"}
allowed_value: {value: "2022"}
allowed_value: {value: "2023"}
default_value: "2021"

}
parameter: month_to_select{
type: string
allowed_value: {value: "Jan"}
allowed_value: {value: "Feb"}
allowed_value: {value: "Mar"}
allowed_value: {value: "Apr"}
allowed_value: {value: "May"}
allowed_value: {value: "Jun"}
allowed_value: {value: "Jul"}
allowed_value: {value: "Aug"}
allowed_value: {value: "Sep"}
allowed_value: {value: "Oct"}
allowed_value: {value: "Nov"}
allowed_value: {value: "Dec"}

}

dimension: start_date {
sql: CASE
WHEN {% parameter month_to_select %} = "Jan" THEN CONCAT({% parameter year_to_select %},'0101')
WHEN {% parameter month_to_select %} = "Feb" THEN CONCAT({% parameter year_to_select %},'0201')
WHEN {% parameter month_to_select %} = "Mar" THEN CONCAT({% parameter year_to_select %},'0301')
WHEN {% parameter month_to_select %} = "Apr" THEN CONCAT({% parameter year_to_select %},'0401')
WHEN {% parameter month_to_select %} = "May" THEN CONCAT({% parameter year_to_select %},'0501')
WHEN {% parameter month_to_select %} = "Jun" THEN CONCAT({% parameter year_to_select %},'0601')
WHEN {% parameter month_to_select %} = "Jul" THEN CONCAT({% parameter year_to_select %},'0701')
WHEN {% parameter month_to_select %} = "Aug" THEN CONCAT({% parameter year_to_select %},'0801')
WHEN {% parameter month_to_select %} = "Sep" THEN CONCAT({% parameter year_to_select %},'0901')
WHEN {% parameter month_to_select %} = "Oct" THEN CONCAT({% parameter year_to_select %},'1001')
WHEN {% parameter month_to_select %} = "Nov" THEN CONCAT({% parameter year_to_select %},'1101')
WHEN {% parameter month_to_select %} = "Dec" THEN CONCAT({% parameter year_to_select %},'1201')
ELSE '20210101'
END ;;
label_from_parameter: month_to_select
}

dimension: end_date {
sql: CASE
WHEN {% parameter month_to_select %} = "Jan" THEN CONCAT({% parameter year_to_select %},'0131')
WHEN {% parameter month_to_select %} = "Feb" THEN CONCAT({% parameter year_to_select %},'0229')
WHEN {% parameter month_to_select %} = "Mar" THEN CONCAT({% parameter year_to_select %},'0331')
WHEN {% parameter month_to_select %} = "Apr" THEN CONCAT({% parameter year_to_select %},'0430')
WHEN {% parameter month_to_select %} = "May" THEN CONCAT({% parameter year_to_select %},'0531')
WHEN {% parameter month_to_select %} = "Jun" THEN CONCAT({% parameter year_to_select %},'0630')
WHEN {% parameter month_to_select %} = "Jul" THEN CONCAT({% parameter year_to_select %},'0731')
WHEN {% parameter month_to_select %} = "Aug" THEN CONCAT({% parameter year_to_select %},'0831')
WHEN {% parameter month_to_select %} = "Sep" THEN CONCAT({% parameter year_to_select %},'0930')
WHEN {% parameter month_to_select %} = "Oct" THEN CONCAT({% parameter year_to_select %},'1031')
WHEN {% parameter month_to_select %} = "Nov" THEN CONCAT({% parameter year_to_select %},'1130')
WHEN {% parameter month_to_select %} = "Dec" THEN CONCAT({% parameter year_to_select %},'1231')
ELSE '20210101'
END ;;
label_from_parameter: month_to_select
}

dimension: datestamp {
type: string
sql: ${TABLE}.datestamp ;;
}

dimension: dim1 {
type: string
sql: ${TABLE}.dim1 ;;
}
dimension: dim2 {
type: number
sql: ${TABLE}.dim2 ;;
}
measure: measure1 {
type: sum
sql: ${TABLE}.measure1 ;;
}
}

In the explore even though I am successfully able to bring the start_date and end_date as user selects year and month and runs the query, but I am unable to use the values to filter the table1 with datestamp >= $start_date and datestamp <= $end_date. The SQL becomes as following with 1=1:


SELECT
my_facts.dim2 AS my_facts_dim2,
CASE
WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
ELSE '20210101'
END AS my_facts_start_date,
COALESCE(SUM(my_facts.measure1 ), 0) AS my_facts_measure1
FROM (Select datestamp,dim1,dim2, sum(measure1) as measure1
from tqspace.tq_ybn_click where is_fraud = 0 AND
1=1 -- no filter on 'my_facts.start_date_filter'
AND
1=1 -- no filter on 'my_facts.end_date_filter'
AND
(tq_ybn_click.dim1 = '5005950')
group by datestamp,dim1,dim2
) AS my_facts

GROUP BY my_facts.dim2 ,CASE
WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
ELSE '20210101'
END
ORDER BY my_facts_dim2
LIMIT 500

Where I want instead is:
 


SELECT
dim2,
CASE
WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
ELSE '20210101'
END AS my_facts_start_date,
COALESCE(SUM(my_facts.measure1 ), 0) AS my_facts_measure1
FROM (Select datestamp,dim1,dim2, sum(measure1) as measure1
from nyDB.table1 where
datestamp >= CASE
WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
ELSE '20210101'
END
AND datestamp <= CASE
WHEN 'Jul' = "Jan" THEN CONCAT('2021','0131')
WHEN 'Jul' = "Feb" THEN CONCAT('2021','0229')
WHEN 'Jul' = "Mar" THEN CONCAT('2021','0331')
WHEN 'Jul' = "Apr" THEN CONCAT('2021','0430')
WHEN 'Jul' = "May" THEN CONCAT('2021','0531')
WHEN 'Jul' = "Jun" THEN CONCAT('2021','0630')
WHEN 'Jul' = "Jul" THEN CONCAT('2021','0731')
WHEN 'Jul' = "Aug" THEN CONCAT('2021','0831')
WHEN 'Jul' = "Sep" THEN CONCAT('2021','0930')
WHEN 'Jul' = "Oct" THEN CONCAT('2021','1031')
WHEN 'Jul' = "Nov" THEN CONCAT('2021','1130')
WHEN 'Jul' = "Dec" THEN CONCAT('2021','1231')
ELSE '20210101'
END
AND
AND
(table1.dim1 = '5005950')
group by datestamp,dim1,dim2
) AS my_facts
GROUP BY A.dim2 ,CASE
WHEN 'Jul' = "Jan" THEN CONCAT('2021','0101')
WHEN 'Jul' = "Feb" THEN CONCAT('2021','0201')
WHEN 'Jul' = "Mar" THEN CONCAT('2021','0301')
WHEN 'Jul' = "Apr" THEN CONCAT('2021','0401')
WHEN 'Jul' = "May" THEN CONCAT('2021','0501')
WHEN 'Jul' = "Jun" THEN CONCAT('2021','0601')
WHEN 'Jul' = "Jul" THEN CONCAT('2021','0701')
WHEN 'Jul' = "Aug" THEN CONCAT('2021','0801')
WHEN 'Jul' = "Sep" THEN CONCAT('2021','0901')
WHEN 'Jul' = "Oct" THEN CONCAT('2021','1001')
WHEN 'Jul' = "Nov" THEN CONCAT('2021','1101')
WHEN 'Jul' = "Dec" THEN CONCAT('2021','1201')
ELSE '20210101'
END
ORDER BY my_facts_dim2
LIMIT 500

 

Any help will be great. 

Thanks

 


This topic has been closed for comments