Question

Seemingly inconsistent SQL generation

  • 19 April 2022
  • 4 replies
  • 111 views

I’m using liquid to dynamically switch the metric being used in a measure based on a parameter that users select on their dashboard.  I’m currently substituting in data into the SQL twice to switch the field being populated as well as the aggregation (sum vs count distinct).

 

Unfortunately I am confused why looker is seemingly populating strings into the SQL statement inconsistently with one string field being wrapped in parentheses and the other not.  For my SQL to work properly I need these strings to not be wrapped in parentheses.

 

In the screenshots below I am trying to understand why the filtered_measure_aggregation (in the red box) is being wrapped in parentheses while the filtered_measure_field (in the green box) is not.

 

Measure:

 

Dimensions:

 

SQL:

Can anyone point me in the correct direction to keep the filtered_measure_aggregation portion from being wrapped in parentheses?

 

My backend is BigQuery Standard SQL if that matters in this instance.

 

Thank you in advance for any assistance provided!


4 replies

Hi Nate ,

Instead of having a separate dimension filtered_measure_aggregation , write its liquid code in the measure dynamic_measure_test .

Another way would be to have two measures dynamic_measure_test_count, dynamic_measure_test_sum
and use them in dimension filtered_measure_aggregation sql tag.

Thanks Adnanjmi,

 

The liquid method is what I ended up having to use.  It definitely works, but this same block of code is used in something like 15 measures in my view.  My hope had been to use that liquid in a single dimension and then use the liquid replacement functionality to push the line of code into each of the measures sql tag.  This way if an update was needed to the aggregations it would need to be made once and would pass on to all the measures.

 

Unfortunately this method does not work because in the instance of the aggregate it wraps the output in parenthesis, while the same method of liquid replacement later in the measure does not wrap the replacement value in parenthesis.

 

I was mostly hoping someone had insight into if it is possible to control the addition of these parenthesis.  It’s a bit frustrating that passing a string through liquid compiles the SQL differently than if you pass a simple dimension vs a dimension that is contained in a function like coalesce.

 

I do appreciate your response though, and hope you have an awesome day!

 

Thank you,

Nate

Userlevel 2

Hi Nates!

 

Hey, I remember this unexpected scenario in one test.

 

Could you upload the code of the “parameter” section?

 

I guess there is a missing character preventing this execution, please take a look at this article:

 

 

https://docs.looker.com/reference/field-params/parameter

 

Please special attention in this part:

 

 

 

Hope it helps!

 

Best regards,

Leo

Hi Leo,

     Sorry for the delay, I just saw your response.  The liquid section is properly populating in the substitution values, it is the section before the liquid portion “Count(Distinct”,   “Sum(“,   etc. that it is wrapping in parenthesis before the liquid portion is applied, causing the liquid section to fall outside of the aggregate function.

 

For reference, here is the parameter though:

 

Thanks for your assistance, but the heart of the matter I am trying to understand is why it is trying to wrap the SQL in parenthesis, but closing that out before the liquid portion is applied, rather than wrapping the entire SQL string in parenthesis.

 

Thanks again,

Nate

Reply