6 Replies Latest reply on Aug 10, 2019 12:34 PM by Don Wise

    Tableau Prep Replace Values and Naming

    none given

      Good Morning,

       

      I'm new to Tableau Prep, after 4 years of Desktop.  So far its intuitive except for a few small items.

       

      I have some survey data with "select all that apply" responses, where the commas in an individual response disrupt the "split" in Tableau (see below).  I was hoping there was a way to pass a separate file into Tableau Prep that contains the actual and corrected response (commas removed), then conduct a mass find and replace.  Currently I'm manually using find and replace in excel.

       

       

      Items_Purchased_Last_Year
      'Groceries such as apples, steak and bananas,Electronics such as iPhones, headphones and TV's'

      'Groceries such as apples, steak and bananas,Toys such as Legos, Nerf and balloons'

      'Electronics such as iPhones, headphones and TV's'

      'Toys such as Legos, Nerf and balloons'

      'Groceries such as apples, steak and bananas,Electronics such as iPhones, headphones and TV's,Toys such as Legos, Nerf and balloons'

       

       

      Lastly, after splitting that column with the corrected response, it will generate multiple columns with a name appended with a "split" number. Upon pivoting those newly created columns how could I merge them back into the same name instead of having the '- split x' name?

        • 1. Re: Tableau Prep Replace Values and Naming
          Don Wise

          Hello None Given,

          You can try a REGEX (Regular Expression) formula to remove commas from a string of text:

           

          Replace Commas

          REGEXP_REPLACE([Items Purchased Last Year],'(\(.*?\))|,','\ ')

           

          If this answers your question , please mark this response as correct.  Best, Don

          Screen Shot 2019-08-08 at 9.12.46 AM.png

          • 2. Re: Tableau Prep Replace Values and Naming
            Joshua Milligan

            Hi!

             

            I really like Don Wise's suggestion for using RegEx. Here's a slightly modified approach that leaves all the commas in place, but replaces the commas that define a split with a vertical bar:

             

            REGEXP_REPLACE([Items_Purchased_Last_Year], "(,)(\w*)( such as )", "|$2$3")

             

             

            The result is a field which you can then split on the vertical bar instead of the commas:

             

            Hope that helps!

            Joshua

            • 3. Re: Tableau Prep Replace Values and Naming
              none given

              That definitely works for the example provided, but I have 50 such responses over 8 columns.  To be simple I only uploaded an example use-case rather than the real one.  I really like the regex idea, but it might be too complicated to identify the exact comma that needs to be replaced in all scenarios.  I'll mark it as correct, unless you have another idea.

              • 4. Re: Tableau Prep Replace Values and Naming
                Joshua Milligan

                That makes sense.  Without examples of the exceptions or a more complete data set, it's a bit hard to know what approach might be best.  But if you have more data, I'd be happy to take a look to think through how to handle it.

                 

                Best Regards,

                Joshua

                • 5. Re: Tableau Prep Replace Values and Naming
                  none given

                  I just added a twbx to the original post with some real data.  If you don't mind could you take a look?  Thanks!

                  • 6. Re: Tableau Prep Replace Values and Naming
                    Don Wise

                    Hello,

                    Based on the type/amount/structure of the data I'd probably use Tableau Prep Builder to clean/shape the data prior to use in Tableau Desktop/Creator.  I've attached a .tfl flow file for you.  Essentially extracted out your data from the .twbx as .csv and brought it into Tableau Prep Builder.  Used 3 separate instances as there appears to be 3 separate categories that are being split off (1) Training Interests (2) Where to Find Non-Traditional Work (3) Why Considered Quitting Non-Traditional Job for survey responses.  It was very easy to simply split off each by comma delimiter and let Prep do the heavy lifting; thereafter a pivot and another clean step to remove null values, lastly aggregating each and then joining them altogether for an output step.  Hope it helps!  Best, Don

                    Screen Shot 2019-08-10 at 12.26.13 PM.png