set expression to count highest symbol in the string

[rob_vander]

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.

[tresesco · Accepted solution]

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

[rob_vander]

@tresesco  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?

[Kushal_Chawda · Accepted solution]

@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)

[rob_vander]

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