Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Liquid Filter in join

  • 16 September 2019
  • 1 reply
  • 222 views
moebe
Userlevel 4

Hi all


I have the following LookML Code


explore: b_al_boe_fact_valuationbase_k_base_reduced {

label: “Postion Based”

description: “In diesem Explore sind alle positionsbasierten Daten ersichtlich”

always_filter: {filters:{field: Reportdate value: “today”}}


join: b_al_boe_dim_position {

type: inner

relationship: many_to_one


sql_on: ${b_al_boe_fact_valuationbase_k_base_reduced.biss_position_spk} = ${b_al_boe_dim_position.biss_position_spk}

    and

{% condition b_al_boe_fact_valuationbase_k_base_reduced.Reportdate%}  b_al_boe_fact_valuationbase_k_base_reduced.biss_valid_rd_from_date {% endcondition %} and

{% condition b_al_boe_fact_valuationbase_k_base_reduced.Reportdate%}  b_al_boe_fact_valuationbase_k_base_reduced.biss_valid_rd_to_date {% endcondition %} and

{% condition b_al_boe_dim_position.Reportdate%}  b_al_boe_dim_position.biss_valid_rd_from_date {% endcondition %} and

{% condition b_al_boe_dim_position.Reportdate%} b_al_boe_dim_position.biss_valid_rd_to_date {% endcondition %}


  ;;

}


The correct SQL I would like to generate would be the following:


SELECT

*

FROM BISS_VIL.B_AL_BOE_FACT_VALUATIONBASE_K_BASE_REDUCED AS b_al_boe_fact_valuationbase_k_base_reduced

INNER JOIN BISS_VIL.B_AL_BOE_DIM_POSITION AS b_al_boe_dim_position ON (b_al_boe_fact_valuationbase_k_base_reduced.“BISS_POSITION_SPK”) = (b_al_boe_dim_position.“BISS_POSITION_SPK”)

WHERE ((b_al_boe_fact_valuationbase_k_base_reduced.“BISS_VALID_RD_FROM” < (TO_TIMESTAMP(‘2019-09-16’))))

AND ((b_al_boe_fact_valuationbase_k_base_reduced.“BISS_VALID_RD_TO” >= TO_TIMESTAMP(‘2019-09-16’)))

AND ((b_al_boe_dim_position.“BISS_VALID_RD_FROM” < (TO_TIMESTAMP(‘2019-09-16’))))

AND ((b_al_boe_dim_position.“BISS_VALID_RD_TO” >= TO_TIMESTAMP(‘2019-09-16’)))


How can I achive that?

1 reply

Krishna_Shukla
Userlevel 1

Hi @moebe , I am not able to understand the reason why you want the date condition in sql join. It’s easy to do it in Lookml way create dimensions for fields in your view


b_al_boe_fact_valuationbase_k_base_reduced.“BISS_VALID_RD_FROM”

b_al_boe_fact_valuationbase_k_base_reduced.“BISS_VALID_RD_TO”

b_al_boe_dim_position.“BISS_VALID_RD_FROM”

b_al_boe_dim_position.“BISS_VALID_RD_TO”


and do date filter while generating look from explore using in range option as given in attached screenshot

looker1792019


We have many filters there e.g. is before, on or after, in range I think these should solve the problem.

Reply


Terms of Service