Is it possible to combine liquid params with a regular sql query in LookML?

nikita42
Participant I

I have a dimension  (StageName) with 10 or so possible values. There are a few different combinations in which a user might want to filter these that are not mutually exclusive, so I’m trying to use a parameter that lists out different filter combos for the user to select.

I’m trying to create a yesno field something like this:

{%if param._param_value == ‘Option1’ and ${StageName} not in (‘Won’,’Lost’)%}  true

{%if param._param_value == ‘Option2’ and ${StageName} not in (‘Pre-Qualified’)%} true

etc..

{%endif%}

The idea is the user can select a single option and I can use the yesno field to filter out multiple values in the StageName making it easier for users.

This syntax doesn’t accept the StageName dimension and if I try it as a regular sql statement it doesn’t accept the liquid param.

Is this even doable? Any advice?

0 6 568
6 REPLIES 6

n_davis
Participant V

Would adding the fields to the ‘Select filters to update when this filter changes’ help narrow down the results easier?

@nikita42 

It's interesting because it's seen in multi-input.

It seems to be a mixture of conditional branching and specific processing.

Can you explain input, proces and output in detail?

nikita42
Participant I

@emoxtom-1637169 So I managed to do this with a mix of LookML and a custom dimension on my Look. In the view file, I have :

parameter: pipeline_view {
type: unquoted
allowed_value: {value: "Won"}
allowed_value: {value: "Lost"}
allowed_value: {value: "Openinc"}
allowed_value: {value: "Openexc"}

dimension: pipe_filter {
type: string
sql: {%if pipeline_view._parameter_value == 'Won' %} 'Won'
{%elsif pipeline_view._parameter_value == 'Lost' %} 'Lost'
{%elsif pipeline_view._parameter_value == 'Openinc' %} 'Openinc'
{%elsif pipeline_view._parameter_value == 'Openexc' %} 'Openexc'
{% endif %};;
}

Then in my custom dimension, I have a case statement like:

if((${sfdc.pipe_filter} = "Won" AND ${sfdc.stage_name} = "Closed Won") OR 
(${sfdc.pipe_filter} = "Lost" AND ${sfdc.stage_name} = "Closed Lost") OR
(${sfdc.pipe_filter} = "Openinc" AND ${sfdc.stage_name}!="Closed Won" AND ${sfdc.stage_name}!="Closed Lost") OR
(${sfdc.pipe_filter} = "Openexc" AND ${sfdc.stage_name}!="Closed Won" AND ${sfdc.stage_name}!="Closed Lost" AND ${sfdc.stage_name}!="Pre-Qualified"),yes,no)

I then use this dimension as a filter on my visualization. So when a user makes a selection on the parameter, this boolean field is reevaluated and filters my data accordingly.

What I’d like to do though, is avoid having to create a string dimension altogether and create the yesno filter directly on LookML using a combination of the param and the stage_name dimension

1st: I don't think the parameter value will be inserted directly into the sql branch. The parameter value is used to ask questions about sql branching.

2st: I think dynamic field (stage_name) can be used here.

# dynamic dimension using templated filters
# filterの条件により主要なカテゴリーが抽出されそれ以外はその他に分類される
filter: select_category {
type: string
suggest_explore: order_items
suggest_dimension: products.category
}
dimension: category_comparison {
type: string
sql:
CASE
WHEN {% condition select_category %} ${category} {% endcondition %}
THEN ${category}
ELSE 'All Other Categories'
END
;;
}

Did you get a hint?

nikita42
Participant I

Isn’t this kind of what I’ve already done? In my LookML, I’m creating a parameter and a matching dimension. I’m then comparing that dimension to a native field  (stage_name) in my data to filter accordingly. You seem to be doing the same thing but using a filter instead of a param.

In the end, I’m still having to do the actual data filtering inside my explore.

leobardor
Participant V

Hi Nikita!

It looks like that you want a combination of all possible matches between ${sfdc.pipe_filter} and ${sfdc.stage_name} .

I suggest creating a dimension thatcombines all possible values with a derived table and then joining this result with your original explore.

Looker - CROSS JOIN

It helped me in one dashboard selection combo box.

Hope it helps!

Regards,

Leo

Top Labels in this Space
Top Solution Authors