Solved: Counting instances across tables

Yobachus · 10-05-2020 12:23 PM

hey there!,
so i have a simple app that has 2 tables.
table 1 has the users add customers, columns are number, name, type (where type is an enumlist of lets say alpha / beta / theta)
Table 2 simply is meant to count how many alphas ,betas or thetas exist

using a virtual column in table 2 i am trying to get a formula that will count the number of times alpha is seen in table 1.

so far my approx formula looks like this:
count(select(table1[type], contains(split(table1[Type], “,”), table2[_THISROW].[Type])))

The problem being of course in the yes/no condition: the split of table1[type] gives me EVERY value in the entire column, so naturally the table 2 value im looking for is going to be in there somewhere, which means its always true, so it selects every single column.

i cant figure out how to make the formula to check just the value for EACH row in table 1 and compare it to see if it has the corresponding table2 value in it (alpha / beta / theta).

Even if i have a nice full list of all the values, i cant figure out a way to count the number of occurences of a specific value IN that list.

If anyone can help modify the formula or has another way of getting around the problem, i would be eternally grateful.

Steve

Try:

COUNT(
  FILTER(
    "table1",
    IN("alpha", [Type])
  )
)

See also:

Yobachus

holy crap, i cant believe that works so well. lol
i tried using IN () of course, but i couldnt see how it was functionally different than contains()
and why does the filter() work when select() doesnt??
i really appreciate you giving me the right answer, im just not 100% sure WHY its the right one!
the only thing i changed was the “alpha” to table2[_THISROW].[Type], so that it can count each value for any number of variables listed in table2 / enumlist.

Steve

IN() looks through a list for an item that matches completely. CONTAINS() looks through a textual value for any occurrence of the search value.

CONTAINS() can be used on lists, but it won’t match items completely. For instance, IN("a", {"aa", "bb"}) is FALSE because there is no item in the list that is just a; but CONTAINS({"aa", "bb"}, "a") is TRUE because a does occur within the textual equivalent of the list.

SELECT() would also work, I just prefer FILTER() in this case.

I’m surprised table2[_THISROW].[Type] worked! I suspect it would also work without table2 there.