9 Replies Latest reply: May 18, 2017 3:58 PM by Stefan Wühl RSS

    count distinct substrings within string

    Nikhila Shankar

      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!

        • Re: count distinct substrings within string
          m w

          See the attached qvw:

            • Re: count distinct substrings within string
              Nikhila Shankar

              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.

                • Re: count distinct substrings within string
                  m w

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

                    • Re: count distinct substrings within string
                      Nikhila Shankar

                      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!

                • Re: count distinct substrings within string
                  Felip Drechsler

                  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.

                    • Re: count distinct substrings within string
                      Nikhila Shankar

                      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!

                    • Re: count distinct substrings within string
                      Dave Riley

                      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

                        • Re: count distinct substrings within string
                          Nikhila Shankar

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

                            • Re: count distinct substrings within string
                              Stefan Wühl

                              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

                                     )

                                )