Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
Hoping someone will be able to help me with this. I have a text box with an expression that evaluates into a long string delimited by commas. For the sake of this example, let's pretend it's this string:
"a,b,c,d,a,a,a,c,f,g"
Is there a way to get the count of distinct substrings within the string if given a delimiter? (i.e. in this case the count would be 6).
Thanks!
Can you change the format of the string, because I think this might be your solution ...
=Count(Valuelist('a','b','c','d','a','a','a','c','f','g'))
Dave
See the attached qvw:
Thanks for the response! I might be missing something from your solution, but the problem I'm running into is that the string I'm trying to split/count is the result of an expression within a text object. The expression evaluates based on other selections in the QVW file, and the string is displayed in the text object. I need to be able to count and display the number of distinct substrings within that text object itself, i.e. I have no way to load the data and split it into substrings in the edit script the way you seem to have done.
You could use a macro to extract the text out of a textbox object and then reload the app to count it.
See the attached file.
Felipe.
Thank you, but I would need to be constantly reloading the file to update the count, since the text box with the string will be updated frequently as users make selections to filter the data. I think I'm realizing what I need is probably some way to get that count in the text box itself with just string handling functions, and it's possible there's no way to do that and I need to come up with something else. Thanks for your input though!
What is the expression in the text object that produces this string?
I didn't want to include it in the post because it's a little complicated and I wasn't sure how much of it was relevant to the question, but here it is:
=
Concat
(
Aggr
(
If
(
Round
(
Sum
(
{<CATEGORY = {'O', 'R'}>} AGE
)
/
Count
(
{<CATEGORY = {'O', 'R'}>} DISTINCT C_NUMBER
)
)
<= 17
,
Concat(DISTINCT C_NUMBER, ',')
)
,
ACTY
)
,
','
)
Basically this is calculating an average age of C_NUMBER (SUM / COUNT), checking if that average age is less than 17, then returning a concatenated list of distinct C_NUMBERs. Those lists of distinct C_NUMBERs are aggregated by a dimension of ACTY, and then concatenated so now I have a full list of relevant C_NUMBERs delimited by commas. The problem is, some of those are duplicates since a C_NUMBER can show up for more than one ACTY, so I wanted to split that string and count the distinct C_NUMBERs. It's possible I'm going about this the wrong way though!
Can you change the format of the string, because I think this might be your solution ...
=Count(Valuelist('a','b','c','d','a','a','a','c','f','g'))
Dave
I think this might be what I'm looking for, thank you!
Another option might be to directly count the distinct values, maybe like
=
Count
(DISTINCT
Aggr
(
If
(
Round
(
Sum
(TOTAL<ACTY>
{<CATEGORY = {'O', 'R'}>} AGE
)
/
Count
(TOTAL<ACTY>
{<CATEGORY = {'O', 'R'}>} DISTINCT C_NUMBER
)
)
<= 17
,
C_NUMBER
)
,
ACTY, C_NUMBER
)
)