topic Re: set expression to count highest symbol in the string in App Development

set expression to count highest symbol in the string

rob_vander — Mon, 05 Aug 2024 11:48:23 GMT

Hi All,

I have below data set

load * Inline [
ID,String
1,a:b:c
2,b:c:d
3,a:b
4,b:c]

I want to count the Number of IDs where String contains most symbol ":". I this example, count will 2 (ID=1,2) because both contains more ":"symbol (i.e 2)

I want to do it in expression as I don't have access to script.

Re: set expression to count highest symbol in the string

tresB — Mon, 05 Aug 2024 12:55:34 GMT

Count({<ID={"=SubStringCount(String, ':')=Max(Total SubStringCount(String, ':'))"}>}ID)

Re: set expression to count highest symbol in the string

rob_vander — Mon, 05 Aug 2024 13:30:16 GMT

@tresB It's working when we have unique values in ID. Actually I have repeated values for ID, so one ID may have multiple String with different length. How do we change set?

Re: set expression to count highest symbol in the string

Kushal_Chawda — Mon, 05 Aug 2024 23:10:59 GMT

@rob_vander In this case, only way is to create Primary key column in script using RowNo() function then use it to display count

Count({<Primary_key={"=SubStringCount(String, ':')= $(=Max(SubStringCount(String, ':')))"}>}ID)

Re: set expression to count highest symbol in the string

rob_vander — Mon, 05 Aug 2024 19:42:17 GMT

@Kushal_Chawda luckily I had a primary key in table so didn't create one but thanks for the solution.