Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Valued Contributor II

Re: count distinct substrings within string

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

9 Replies
mwoolf
Honored Contributor II

Re: count distinct substrings within string

See the attached qvw:

Not applicable

Re: count distinct substrings within string

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
Valued Contributor III

Re: count distinct substrings within string

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

Re: count distinct substrings within string

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!

mwoolf
Honored Contributor II

Re: count distinct substrings within string

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

Not applicable

Re: count distinct substrings within 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!

flipside
Valued Contributor II

Re: count distinct substrings within string

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

Re: count distinct substrings within string

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

MVP
MVP

Re: count distinct substrings within string

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

       )

  )

Community Browser