Loop through list of fields using Liquid to check _in_query

[tmiguel]

I’m trying to loop through an array of strings that represent field names in my view to check if each field name is included in a query and if so append that field name as a string to a new variable.

The goal here is to build a derived table based on what a user selects in a query.

Currently, I have to hard code in each `_in_query` check so it’s a mess of duplicate code since we’re checking 10+ fields and this list could grow.

Here is what I attempted:

In LookML file:

```

{% assign potential_grouping_dims = 'field_name_1,field_name_2,field_name_3’ | split: ',' %}

{% assign grouping_dims = ‘’ %}

{% for dim in potential_grouping_dims %}

    {% assign assigned_dim_in_query = 'view_name_1.' | append: dim | append: '._in_query' %}

    {% if assigned_dim_in_query  %}

        {% assign grouping_dims = grouping_dims | append: dim | append: ',' %}

    {% endif %}

{% endfor %}

```

However, it appears that when I try to use liquid to loop through and check if the field is in the query `{% if assigned_dim_in_query  %}` always evaluates to TRUE since `assigned_dim_in_query` evaluates to `’view_name_1.field_name_1._in_query’` (a string) rather than being evaluated as specific LookML variable `view_name_1.field_name_1._in_query`.

Here is what works (static check for each field)

```

{% if field_name_1._in_query = ‘’ %}

    …

{% endif %}

{% if field_name_2._in_query = ‘’ %}

    …

{% endif %}

{% if field_name_3._in_query = ‘’ %}

    …

{% endif %}

```

Is it possible to use a liquid array to check if a field is in a query so that we don’t have add a bunch of duplicate LookML?

[IanT]

Sorry I am not going to help with the actual question but would be very interested as to understand why you are doing this?

Maybe then I could throw in some suggestions.

[tmiguel]

@IanT The purpose of grouping and filtering a derived table based on what dimensions the user has selected. This is needed because we need to calculate our denominator separate from the numerator in a single query. Therefore, a simple join of two views into one explore is not sufficient without pre-aggregation and filtering of our denominator view.

For example, if I have:

Tables:

Denominator View: table A: Member Info

MemberID	YearMonth	State
101	2022-01	NY
102	2022-01	NY

Numerator View: table B: Medical Service Info

MemberID	YearMonth	Medical Service Category	Medical Service Dollars
101	2022-01	Facility Inpatient	$4,000
101	2022-01	PCP Visit	$100

Relationship:

• 1:m denominator:numerator
• where denominator.MemberID = numerator.MemberID and denominator.YearMonth = numerator.YearMonth

Output / what we Expect:

The denominator should never be more granular than what is available in member info. Therefore when a user selects:

select mi.YearMonth
     , mi.State
     , ms.Medical_Service_Category
     , sum(ms.Medical_Service_Dollars) as total_med_dollars
     , count(distinct mi.MemberID||mi.YearMonth) as count_of_member_months
     , total_med_dollars / count_of_member_months as dollars_per_member_per_month
from explore_1
group by mi.YearMonth
       , mi.State
       , ms.Medical_Service_Category

We expect:

YearMonth	Medical Service Category	Total Medical Service Dollars	Count of Member Months	Dollars per Member Per Month
2022-01	Facility Inpatient	$4,000	2	$2,000
2022-01	PCP Visit	$100	2	$50

count_of_member_months should never be more granular than what is available in the Member Info table. Meaning, we should never split the members by Medical Service Category.

Usually in SQL we handle this as a CTE pre-aggregating the Member info before joining onto whatever table we’re trying to get the numerator from.

Back to the Loop issue:

Member Info could be 1, 10, 20+ columns, but the check to see if we’re using it in the query is essentially the same. Meaning:

1. check view_name.field_name._in_query
2. if yes, then add field to group by in derived table

Then the rest of the process runs as expected...

1. Aggregate derived table
2. Join onto numerator view (done in explore file)
3. Calculate metrics (already build in numerator view file)
4. Output results to user

If we could build a loop to check if the field is in the query that would make this much easier to maintain.

NOTE: I simplified the example to just focus on in_query. We actually use is_selected and is_filtered to separately build the where clause and select statement/group by.