8 Replies Latest reply on Nov 1, 2017 11:45 AM by Tyler Garrett

    Count of expressions within a string

    Hannah Moore

      I have a field in my data source that looks like this:

      Group A, Group B

      Group B

      Group A

      Group A

      Group A, Group C

      Group C, Group B

      Group C

      Group A

      Group C

      etc... (x 100's of groups)

      I am wondering if there is a formula or something I can use to give me a count of each group in the field without having to do separate calculated fields for each group?  My data source is dynamically updated so I don't want to have to make modifications to the data source to get this functionality.

       

      The results I'm looking for would be a count of each group:

      Group A     5

      Group B     3

      Group C     4

       

      Any help would be greatly appreciated!

       

      Thank you.

        • 1. Re: Count of expressions within a string
          Nicholas Hannan

          Hi Hannah,

           

          If you don't want a separate Measure for each group, I believe you would need to alter the datasource in some form. You would want it in a form where the field isn't multi-valued.

           

          Here's an option you might consider.

           

          Split a Field into Multiple Fields

          • 2. Re: Count of expressions within a string
            Hannah Moore

            Thanks Nicholas.  I was hoping for some sort of cross-join type solution if possible as I don't want to have to alter my underlying source.  I do have another data source with all of the various "Groups" as separate rows that I could join to this. Any further suggestions?

            • 3. Re: Count of expressions within a string
              Deepak Rai

              Hi Hannah,

              Using the method described by kettan here:Cross-Join, Legacy Connection & Custom SQL: How can I use 'LIKE' in a RegEx-like way to unpivot my data? , I could get upto here and I am stuck upon aggregating individual Groups to give you 5 for A , 3 for B and 4 for C. May be someone like Kettan would pick it from here to give you final solution.. See attached and screenshot. Looks like I might be near to it, but somehow, something not working at this moment.

              Thanks

              Deepak

               

              • 4. Re: Count of expressions within a string
                kettan

                My data source is dynamically updated so I don't want to have to make modifications to the data source to get this functionality

                This need might possibly also close the door to the cross join methods shared below,

                but whatever, here they come 

                 

                 

                THE KNOWN ITEM METHOD

                This method cross joins against a table with all of the groups and thereafter applies a "calculated join", namely:

                 

                CONTAINS([Group List],[Group])

                 

                 

                THE KNOWN NUMBER OF ITEMS METHOD

                This cross joins against a big enough dummy table and thereafter applies a "calculated join" that counts the number of separators + 1 to find out how many items the string [Group List] contains. This is the formula used in attached workbook:

                 

                [Item] <= LEN([Group List]) - LEN(REPLACE([Group List],",","")) + 1

                 

                This method is more complicated because it also needs to extract the values to display with another function:

                 

                MID(

                ", "+[Group List]+",",  // string

                FINDNTH(", "+[Group List]+",", ",", [Item])+2,  // start

                FINDNTH(", "+[Group List]+",", ",", [Item]+1) - FINDNTH(", "+[Group List]+",", ",", [Item]) - 2  // length

                )

                 

                See more in screenshots below and attached workbook and Excel file.

                 

                 

                SCREENSHOTS :  DATA + REFERENCE TABLES

                Row Split - Known Items - Known Number of Items - thread 242907 data.png

                 

                 

                SCREENSHOTS :  JOIN

                Row Split - Known Items - Known Number of Items - thread 242907 cross join.png

                 

                 

                SCREENSHOT :  KNOWN ITEMS

                 

                Row Split - Known Items - Known Number of Items - thread 242907 known items.png

                 

                 

                SCREENSHOT :  KNOWN NUMBER OF ITEMS

                Row Split - Known Items - Known Number of Items - thread 242907 known number of items.png

                 

                 

                 

                Ps.

                 

                Lately, I have spent too much time on this forum and intend to put a stop to it now. Future pings might tempt me to read the comment, but please don't expect any feedback on it. One thing that makes it easier not to be tempted is to be without a license. My trial version of Tableau ends in 2 days. After that, I can't open packaged workbooks unless version 9.0 or older - not even the workbook I just attached 

                 

                Row Split - Known Items - Known Number of Items - thread 242907 tableau trial ends in 2 days.png

                 

                 

                 

                Attached Workbook Version:   10.3

                4 of 4 people found this helpful
                • 5. Re: Count of expressions within a string
                  Shawn Wallwork

                  kettan wrote:

                   

                  Lately, I have spent too much time on this forum and intend to put a stop to it now. Future pings might tempt me to read the comment, but please don't expect any feedback on it. One thing that makes it easier not to be tempted is to be without a license. My trial version of Tableau ends in 2 days. After that, I can't open packaged workbooks unless version 9.0 or older - not even the workbook I just attached

                   

                  Row Split - Known Items - Known Number of Items - thread 242907 tableau trial ends in 2 days.png

                   

                   

                   

                  Attached Workbook Version: 10.3

                   

                  Sorry to hear that kettan.  A sad day indeed. Thanks for all you've done throughout the years. You are a much valued member of our community!

                   

                  --Shawn

                   

                  Tracy Rodgers Patrick Van Der Hyde Amanda Boyle

                  2 of 2 people found this helpful
                  • 6. Re: Count of expressions within a string
                    Deepak Rai

                    Great Work Kettan!!!

                    Thanks

                    Deepak

                    1 of 1 people found this helpful
                    • 7. Re: Count of expressions within a string
                      Hannah Moore

                      Thank you so much @kettan !!! This is exactly what I needed - I very much hope to continue to see you on the forums - you've provided a very elegant solution to a complex issue.

                       

                      Thank you again!

                      1 of 1 people found this helpful
                      • 8. Re: Count of expressions within a string
                        Tyler Garrett

                        Good stuff mate. I love your work that I've seen thus far.

                         

                        Stay in touch? itylergarrett@gmail.com - i of course don't want you to leave... we need more rabbit hole solutions that offer deep insights (product feeedback) - not just a PASTE from onlinehelp and boring correct answer goes to the least helpful answer. Grrr. It can get rightfully frustrating mate!

                         

                        loads of Jedi left the forums, most I know quit due to questionable Q/A platform (right answer going to wrong people, lean accounts only ask one question and never login again... correct answer goes to a third party company far too often, so the friends and Jedi I've know  stopped coming here), and I do what i can to harass them on other social platforms - lol   .

                         

                        Shawn for example is utterly brilliant

                         

                        Text messages too, I don't hold back :-). Happy to Network with you

                         

                        Please message me. Would love to connect. Have a good week Kettan. Enjoy  

                         

                        I do do think we can plug you into someone at tableau - and then you're voice can carry the weight of myself, and hopefully we get your brilliant ideas to product managers - sometimes content is lost here behind a sea of other brilliant people...

                         

                        alright take care <3 later fam

                        1 of 1 people found this helpful