7 Replies Latest reply on Mar 3, 2017 2:41 PM by Aseem Sharma

    Counting similar text in a delimited string

    Aseem Sharma

      I have 1 column with a variable length comma separated string eg.

       

      x,a,c,d,f

      c,d

      d,a,c,y,p,x

      a,c,d

      d

       

      trying to count the occurrences of each alphabet.  Final result

      x = 2

      a = 3

      c = 4

      d = 5

      f=1

      p = 1

      y =1

       

      Anyway to accomplish this in tableau?

        • 1. Re: Counting similar text in a delimited string
          Tom W

          If you had a string like x,a,x would you need that to count X once or twice?

          If you want it to count once, just create a calculation like this for each letter of the alphabet: COUNT(CONTAINS([YourField],'x'))

          If you need to calculate multiple occurrences within the same string, create a calculated field for each letter of the alphabet like: SUM(len([yourstring])-len(replace([yourstring],'x','')))

          • 2. Re: Counting similar text in a delimited string
            Aseem Sharma

            Thanks for the reply.

             

            x would count as 2.

            but x's and a's are dynamic values, so i cant specify them in COUNT(CONTAINS([YourField],'x'))

            • 3. Re: Counting similar text in a delimited string
              Tom W

              How is Tableau meant to know what you want to count then? Please explain your requirement better and provide a better example of your data as an attached Tableau Packaged Workbook with that sample data.

              • 4. Re: Counting similar text in a delimited string
                Aseem Sharma

                This is a sample data set, column contains a  comma separated string.

                String can contain 200 different types of phrases, always comma seperated

                 

                Time and a half

                Time and a half,zoomtext

                Separate room

                Time and a half

                Time and a half

                Separate room,double time

                Time and a half

                Time and a half

                Time and a half

                Time and a half

                Separate room,time and a half

                Separate room,time and a half

                Separate room,time and a half

                Separate room,time and a half

                Time and a half

                 

                Trying to Get to

                Time and a half = 13

                zoomtext = 1

                Seperate room = 6

                double time = 1

                 

                Hope this clarifies things

                • 5. Re: Counting similar text in a delimited string
                  Tom W

                  Effectively it sounds like you want to Tableau to scan all rows and extract a distinct list of terms which occur in each row, separated by a comma.

                  Then for those distinct terms, count the number of instances.

                   

                  Unfortunately, this is isn't possible. You either need to create calculated fields looking for the instances like I demonstrated in my first reply or you need to preprocess so the strings are separated out onto individual rows. You could write some SQL to do this preprocessing if you're connecting to a database, otherwise you'll need to look into using a script or tool to do that pre-processing some other way.

                  • 6. Re: Counting similar text in a delimited string
                    Aseem Sharma

                    Thanks for looking into this.

                    • 7. Re: Counting similar text in a delimited string
                      Aseem Sharma

                      Couldnt do this in tableau, but used the following sql to split data into multiple rows then added that as a new data source

                       

                      SELECT T1.reference, T1.name, T2.my_Splits AS subjects, T1.subtitile
                      FROM
                      (
                       
                      SELECT *,
                        CAST
                      ('<X>'+replace(T.subjects,',','</X><X>')+'</X>' as XML) as my_Xml
                       
                      FROM [yourTable] T
                      ) T1
                      CROSS APPLY
                      (
                      SELECT my_Data.D.value('.','varchar(50)') as my_Splits
                      FROM T1.my_Xml.nodes('X') as my_Data(D)
                      ) T2

                      1 of 1 people found this helpful