Solved: Count how many times an item appear in a text list...

Steven_Aung · 07-16-2020 01:55 PM

Say I have a list LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”) and want to know how many “A” in the list. How can I do that with expression?

Few things I found out so far:

Using ‘-’ sign will remove any duplicate from the list. LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”) - LIST(“A”) will give me [“B”,“C”,“D”]. “B” only appeared one time.
INTERSECT remove duplicates too. INTERSECT({‘A’, ‘A’, ‘B’}, {‘A’, ‘A’, ‘C’,‘C’}) will return just ‘A’

Steven_Aung

There is a bit simper solution:

IF(ISBLANK([LIST]), 0, COUNT(SPLIT(SUBSTITUTE([LIST] ,"SEARCH_TEXT","@"), "@"))-1)

If “SEARCH_TEXT” is not in the list, the split will still return one item list. So ‘-1’

** Assuming that 'SEARCH_TEXT" must be unique. If any longer text having ‘SEARCH_TEXT’ as sub-string, this solution will not work.

View solution in original post

Austin

Haven’t tried but something like COUNT(SELECT(TABLE[Values], [Values]=[Value I want]))? This would only work if you can somehow know what you want to count and wouldn’t be useful for something like finding all duplicated values.
(Disclaimer I have not attempted this solution and it might not be useful for your case)

Steven_Aung

Thanks for the reply. It’s a text list and not a reference list. I also don’t want to use SELECT / FILTER in a virtual column. It’ll just slow things down.

Bahbus

There is no quick and easy way to do this. It would be significantly faster and more efficient to do with with Refs and SELECTs.

SheikhSajad

Works for me Thanks

Austin

Select does not slow apps down much unless dealing with very large datasets, in the 10k+ range.
I do not believe there is currently a method that allows you to know how many occurrences of an item there are in a list. What is your use case for counting an item in a list? Might be another way to accomplish the goal.

Koichi_Tsuji

What about this?

count(LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”) )-len(substitute(substitute(text(LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”))," , ", “”), “A”, “”))

I think you can twist this expression a bit to make it dynamic rather than static

Austin

Interesting, this would only work on string items length 1 though right?

Koichi_Tsuji

Correct.

Bahbus

Depending on the complexity of the individual items within the list, data loss could occur because of smashing all the letters together and then accidentally substituting from the combined string.

Edit: Well, not data loss. Just incorrect results.

Koichi_Tsuji

I’m just in line with his query not assuming complex case.

Vishnu_Tiwari

How to highlight duplicate Value in appsheet ? Please Help

GreenFlux

This can be done using EXTRACTMENTIONS() if you add an “@” to the beginning of your pattern.

COUNT(EXTRACTMENTIONS(SUBSTITUTE([ColumnToSearch],"Pattern","@Pattern ")))

Bahbus

I was staring at these while writing my first reply thinking “if only there were a way to use extract here somehow”.

Now, does it work if there are spaces in the pattern? Or would the spaces need to get subbed out?

GreenFlux

You can search for a pattern with a space, and then just replace it with an underscore so the EXTRACTMENTIONS() part will work.
COUNT(EXTRACTMENTIONS(SUBSTITUTE([ColumnToSearch],"Patt ern","@Patt_ern ")))

Keep in mind, we’re not actually writing the substitute back to the sheet. Just changing the pattern to count it.

Steven_Aung

That works. Thanks.

Some kind of counting an item from a list should be build in expression.

Bahbus

Ok. Yeah. That’s what I was referring to. You have to sub the space in the pattern one way or another for it to work.

Edit: or, rather, what I mean is, if your data has, or might have, spaces in it, you need to account for them in some way in regards to using this extractmentions method.

GreenFlux

It looks like some guy name Patt Ern is getting notifications.

The forum made it bold and tagged it lol.

Steven_Aung

There is a bit simper solution:

IF(ISBLANK([LIST]), 0, COUNT(SPLIT(SUBSTITUTE([LIST] ,"SEARCH_TEXT","@"), "@"))-1)

If “SEARCH_TEXT” is not in the list, the split will still return one item list. So ‘-1’

** Assuming that 'SEARCH_TEXT" must be unique. If any longer text having ‘SEARCH_TEXT’ as sub-string, this solution will not work.

Steven_Aung

Since @GreenFlux’s and my solutions are based on SUBSTITUTE, the search text must be unique. Otherwise, it won’t work.

It will work for my particular case.

So I will create a feature request.
Thank you guys for the helps.

Steven_Aung

is the feature request.

Bahbus

COUNT(
 EXTRACTMENTIONS(
      SUBSTITUTE(","&[TextList]&",", ","&[Pattern]&",", ",@"&SUBSTITUTE([Pattern], " ", "_")&",")
 )

)

I feel like this should work pretty much always.

Edit: updated. All you need to do is include the delimiter in the search string and remember to keep it in the replacement string.

I made this real quick as an example. Test data has a mix items with spaces and not. Example also passes the pen/pencil test case, and last item case.
https://www.appsheet.com/samples/?appGuidString=90b92b83-6123-403b-9b04-287491f21d94

Steven_Aung

What happen if [Pattern] is post fix of a longer string?

Example “Something Pattern,Pattern”

Another thing is “Pattern” could be end of the list.

It’s doable but without native support but the solution will be convoluted.

Bahbus

Fixed by appending the delimiter to the end of the [ColumnToSearch].

I am unsure what you mean. Like if list is:
Cat, Cat, Dog, Mouse
Search and Count:
Cat, Dog
?

In my example, I use a drop down of the list to help you pick what item you want to count to eliminate what I think you’re talking about.

Steven_Aung

Seems like your solution could work for cases I can think of.

Not sure why you have to substitute space with underscore?

Bahbus

Alrighty.
So in my sample app ^, I set up a column as an EnumList - just for easy adding/editing the list.
Another column that uses the list for valid if values to select a pattern, so that you can only count full items in the list - so no partial or fuzzy counting.
A VC to reformat that list into a Text string to work with.
And another VC with a slight change but otherwise the same formula originally posted by @GreenFlux, targeting the VC with the reformatted list.

Works for 1st item, last item, pen vs pencil, several sequential of the pattern, mixes of spaces, and anything else I could think of.

Bahbus

It doesn’t have to be an underscore, per se. But in order for extract to pull a mention correctly, each “item” in the list can’t have spaces in it. So you could just remove it a different way like SUB([List], [Pattern], “@howdoyoudogoodsir”) and skip my more complicated sub. But you know, ideas.

ABHIJEET_PATIL

COUNT(select(Table[Name], [Name]=[_ThisRow].[Name]))

This worked for me

daudaxx

Dear all,

my case is the following, talking about a Guestlist to be maintained for an event:

1. I have a "MASTER" table with NAME | EMAIL | CONTACT PERSON EMAIL
2. in a separate spreadsheet (already referred in the app data source) an "ACCREDITATION QTY" table with CONTACT PERSON EMAIL | GRANTED QUANTITY

In order to check that the Contact Person (CP) does not give away more than the granted qty of free passes, I basically need to count how many records with the same CP are being added in the Master list (via the "form" view of the app I'm building) against the Granted Qty assigned in the "ACCREDITATION QTY" for that CP.

What is the formula that allows me to count how many times that same CP email address appears in the MASTER table, and then check if it doesn't exceed the limit defined in ACCREDITATION QTY table?

Thank you very much for your support,

Daniele

AleksiAlkio

It depends where you are using it and what column types you have, but in generally it would be something like..
COUNT(SELECT(Master[KeyColumn],[Contact Person Email]=USEREMAIL()))<=
LOOKUP(USEREMAIL(),"Accreditation Qty","Contact Person Email","Granted Quantity")

daudaxx

Dear Aleksi, thank you very much for your reply. You gave me hope 🙂
The fact is that I tried and tried again and again, but it's still not working.
1. I applied the formula to the ADD action button, so that once the limit is reached and the formula is not true, the button disappear and the user cannot add new guests
2. I checked that the "Granted quantity" is a Number field type (so that is comparable with the count made by the formula)
3. I changed it to only < (instead of <=), because if the limit is 2, and there are already two records, I want the formula to already be not true
4. when I click on "Test" near to the formula, it returns me this (empty screen):

Is there a way to debug the formula, and understand what is the value returned by each of the two steps (count and lookup)?

Thanks a lot,

Daniele

AleksiAlkio

You can easily check them both if you add two virtual columns and you use COUNT(..) or LOOKUP(..). Then you can find out what those two numbers are.

Count how many times an item appear in a text list (not ref list)

This can be done using EXTRACTMENTIONS() if you add an “@” to the beginning of your pattern.