Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

count distinct substrings within string

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!

1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

9 Replies
m_woolf
Master II
Master II

See the attached qvw:

Not applicable
Author

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.

felipedl
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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!

m_woolf
Master II
Master II

What is the expression in the text object that produces this string?

Not applicable
Author

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!

flipside
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

I think this might be what I'm looking for, thank you!

swuehl
MVP
MVP

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

       )

  )