Tip for how to filter a table then count occurrences, please!

[Simon_Hill]

Looking for tips please for the best way to tackle this seemingly easy challenge:

Consider my App will have a data sheet, like the one below:

The goal is to allow the user to

1. Select which attributes (top row) are of interest (1 or more)
2. Select the values of those attributes to use as a filter (1 or more)

then show the count of people that match.

e.g. If I am interested in people with favorite flavor vanilla then the count is 2.
e.g. if I am interested in YOUNG people with favorite flavor vanilla then the count is 1.

A key restriction is that in reality the table of data is not based on a fixed database. Users will need to be able to work with a fresh table (in practice I will import this into the Google sheet). The layout of the table will be the same (people down the side and attributes across the top), however the number & definition of the attributes may vary considerably.

Is there a way to achieve this without formulas in the Google sheet?

I have managed the first hurdle, selecting attributes and picking which of their values are needed. But once the filters are defined, I’m getting stuck with the final count step. Is it too much for Glide since relations cannot easily be filtered without creating template columns? That doesn’t seem feasible because of the scale.

If the best option is using a Google formula, can anyone suggest the formula please?!

[Manan_Mehta]

To count the number of relations that are created you can use the rollup column. That should help you deal with this without any Google Sheets formulae.

https://docs.glideapps.com/all/reference/data-editor/computed-columns/rollup

[ThinhDinh]

How many attributes do you plan to have?

I’m thinking we can do it like this.

Create a separate table where you have only 1 row to store the choices in user-specific columns. Let’s say you have a “Color choice” column.

Then bring that color choice column over to the main table using a single value column.

Add an If Then Else column to take care of the empty choices, let’s call it Color ITE. If Color choice is empty then choose the Color from the original Color, else Color choice.

After having all those Color ITE, Age ITE, Flavor ITE column then you can make a final ITE column to see if the whole row matches the filter. Let’s call it “Final ITE”.

If Color is not Color ITE then false, Age is not Age ITE then false, …, else true.

Make a template column with the value ‘true’, relating that to the whole Final ITE column and add a rollup to count the total number of matches.

[Darren_Murphy]

This something that I do in my apps all the time. I described the technique that I use below:

Array to array relation with AND condition

I generally use a combination of templates and multi-relations for this type of dynamic filtering. The basic idea is: In the table that contains your source data, create a template column that concatenates each of the values that you’ll be filtering on. In your example, it might look something like: House:5 BR:3 WC Apartment:1 BR:1 WC Condo:3 BR:2 WC etc.. Next create an identical template that concatenates the same values from your User Specific Columns Finally, create a multi-relation tha…

Essentially it boils down to dynamically generating a multi-relation for displaying the filtered results, and doing a rollup through that relation to get a dynamic count. It works very well.

[Simon_Hill]

HI Darren,

Yes, I have used that option myself and it works well for a limited number of fixed attributes. But I don’t think it works here. In this case, I may have 50 attributes and the names of the attributes will change. How do you get the name of the attribute into the concatenated template when so much is fluid?

[Simon_Hill]

Thanks for responding… but I hit the same issue as per response to Darren.

[Darren_Murphy]

@Simon_Hill

Yes, I have used that option myself and it works well for a limited number of fixed attributes. But I don’t think it works here. In this case, I may have 50 attributes and the names of the attributes will change. How do you get the name of the attribute into the concatenated template when so much is fluid?

Yes, as I mentioned in my original post, it works well for filtering with up to 4 or 5 attributes. After that it can get a little cumbersome. Although, that really depends on whether or not you need to provide an “All” option in your choice components. If you don’t, it should scale up quite well - although you do need to predefine which columns will be used for filtering in order to create the template columns.

It is also possible to dynamically generate the options for the choice components based on the existing data. That can be done as follows:

• Ensure that you have a Row ID column in the table that contains your data
• For each of the columns that will be used, do the following:
  • Create a single relation that joins that column to itself
  • Create a lookup through that relation that fetches the Row ID
  • Now create an if-then-else column that compares the value in the lookup column to the actual Row ID
  • For any that match, return true
  • You can then use that column to filter the list in a choice component

Again, it will mean quite a few columns if you have a large number of filters to apply, but it will work.

[Simon_Hill]

Imagine doing that for 50+ columns?! I think cumbersome is being kind

I am beginning to think that using a Google formula is the way to go, this is getting close:

How to Use the FILTER Function in Google Sheets | Multiple Columns

[Darren_Murphy]

Filtering on 50+ attributes is going to make for an “interesting” UI

[Simon_Hill]

Yes, that’s true. In reality the people are patients. The attributes make up a range of descriptors. All the factors that go into making a treatment decision. As you can imagine, experts need the flexibility to select a few of those factors and filter on some values of each…

[Simon_Hill]

Well I think I found an alternative solution that is quite efficient.

1. Put the data into a sheet called ‘Data’

2. Create a new Google sheet called ‘Choices’ with identical headers as the Data sheet, e.g. by using the formula in A1 '=Unique(Data!A1:Z1) - this is good for 26 headers.

3. Now create a form button in Glide with new rows created in the Choices sheet (under the headers).

4. I imagine a form with 26 choice components. Each component has the title Attribute1, Attribute2, etc. The data source should be successive columns in the Data Sheet. The destination column should be the respective column in the Choices sheet. In this way, a new row is created containing 1 filter option per attribute.

5. Create a new Google Sheet called Results. As in step(2) ensure the 1st row has identical headers.

6. In cell A2 of the the Results sheet we now apply the filters to the original data set using the simple formula: =Filter(Data!A2:Z,Data!A2:A = Choices!A2, Data!B2:B = Choices!B2)
   The formula is shown for the first 2 attributes A and B but the rest of the 26 can be added respectively. Finally a roll-up column will deliver the count.

7. If more than one filter option needs to be considered then it is possible to add another row of filter options using the form component. The formula in (6) then just needs to have extra conditions added. I admit I haven’t worked out the formula that accounts for a ‘Filter 1 or Filter 2’ but it should be possible.

And the beauty of this approach is that it does not even use a single relation or a single template component!

I just tried it and it and it works!

[Darren_Murphy]

Nice job

[Simon_Hill]

I’m going to drop this in as an excellent resource for anyone that needs help to generate the Query formula in your Google sheet. I have installed the ad in and it works really well. The only downside is that you cannot have a query field that is an empty cell, but it works for most instances.

Multiple Vlookup Matches add-on for Google Sheets