7 Replies Latest reply on Dec 2, 2016 11:41 AM by Joe Oppelt

    String Calc to group similar items

    Andres Galban

      Can anyone recommed a calc that groups  similar values based on the first word (before a space) or based on the first X amount of characters...thanks

       

      EDIT: Also if there are single values containing spaces ideally i would need to maintain these intact .

       

      example:

       

       

      Header 1
      candy apples
      candy corn

      chocolate cake

      chocolate mousse
      chocolate cookies
      strawberry shortcake
      strawberry cheesecake
      strawberry jam
      apple tart

      apple pie

      Fruit Loops

       

      to Calc Field:

      Candy

      Chocolate

      Strawberry

      Apple

      Fruit Loops

       

      Message was edited by: Andres Galban

        • 1. Re: String Calc to group similar items
          Joe Oppelt

          One idea would be this:

           

          IF FIND([Header 1], " ") = 0 or FIND([Header 1], " ") > 10  // FIND the first space

                                                           // if zero, then no space.  If greater than 10, truncate at 10

          then

          MID([Header 1], 1, 10)   // chop off at 10 characters.  (MID grabs a substring)

          else

          MID([Header 1], 1, FIND([Header 1], " ")-1 )  /chop from position 1 to the character before the space.

          END

           

          The one thing I didn't account for is if the first space is in position 1.  If that can happen, then you'll want to account for that.

          2 of 2 people found this helpful
          • 2. Re: String Calc to group similar items
            Joe Oppelt

            You can find all sorts of string functions detailed here:

             

            String Functions

            • 3. Re: String Calc to group similar items
              Andres Galban

              Thanks Joe. This is helpful. However I realized I was not specific enough with my example... I also have some single values with spaces that I need to keep intact. Not sure if this would work in such cases.  See the edit.

              • 4. Re: String Calc to group similar items
                Phillip Overpeck

                If you are looking for the first word before a space you can use the split function.

                • 5. Re: String Calc to group similar items
                  Joe Oppelt

                  You're going to make this hard on yourself. 

                   

                  Is "Fruit Loops" the only space-embedded value?  (I'm guessing not...)  You'll have to figure out a way to tell the calc I suggested to handle certain special cases differently.

                   

                  IF STARTSWITH([Header 1], "Fruit loops"] or STARTSWITH([Header 1], "Candy Corn") then

                  (do something different)

                  ...

                   

                  And maybe then you can use the FINDNTH() function instead of the FIND() function to find the 2nd space.  (Likewise, the 3rd space, when your data includes "M and M", etc.)

                   

                  And you're doing all this because you want Tableau to build this dimension for you.  If your data set is going to cause more exception handling than it's worth, maybe you should create this when you are creating the data source, rather than inside Tableau.

                  1 of 1 people found this helpful
                  • 6. Re: String Calc to group similar items
                    Andres Galban

                    Thanks for the reply. Unfortunately I do have a lot of  single values that contain spaces that need to remain intact, and i'm sure many will be added in the future so keeping an exceptions list would not be feasible. The issue stems from bad input on the user side and sadly i do not control the data source.

                    Basically the logic i had thought about in my head was.. If multiple values exist that share the first word (meaning all characters before the first space), then truncate all characters after first space. Else if a value contains a space but does not share the characters with any other value, do not truncate after first space.

                    Looks like its a bit trickier than I originally thought, but regardless the calcs you suggested are helpful. Thanks!

                    • 7. Re: String Calc to group similar items
                      Joe Oppelt

                      You might be able to get that with LOD calcs.

                       

                      But first, tell me a bit about your data source.  Is it a TDE?  (Tableau Data Extract.)  Maybe there is a way to pre-process some of this in the creation of the data source.

                       

                      What I'm thinking about is this:  If your data can come in with an extra column that has truncated the field at the first space, then you can do something like this:

                       

                      { FIXED [Truncated Header] : COUNT([number of records]) }

                       

                      FOr each truncated value, you would get the count.  (And the FIXED LOD does its work before filters on the sheet are applied, so you will be looking at all records, not just the filtered set.)

                       

                      Then,

                       

                      if [Fixed Calc] = 1 then [Original Header] else [Truncated Header] END