5 Replies Latest reply on Nov 30, 2017 2:57 PM by Yuriy Fal

    Problem with R strsplit - 'unexpected type' error

    James Martin

      Hi everyone,

       

      I have a field called searchTerm that contains a string that can be any number of words long.

       

      For example:

       

      "search term one"

      "this is my search".

       

      I want to transform this field into a new field that puts each individual word into it's own row.  So the above would become a list like:

       

      search

      term

      one

      this

      is

      my

      search

       

      I tried the following to do this in Tableau using R. 

       

      SCRIPT_STR("strsplit(.arg1, ' ')",attr([searchTerm]))

       

      However, I get the following error: "The result returned by the SCRIPT function is of an unexpected type.

       

      I'm not overly familiar with R - can anyone point me in the right direction?

       

      Thanks

        • 1. Re: Problem with R strsplit - 'unexpected type' error
          Yuriy Fal

          Hi James,

           

          The error you get from running your R script is generated

          because the STRSPLIT() function returns a list as a result,

          but Tableau is expecting a vector as a result.

           

          So formally one need to transform a list to a vector first,

          then return it back in Tableau.

           

          But there would be another complication because of that.

          Tableau is expected the resulting vector(s) to be the same size(s)

          as the addressing window(s) in the initiator Table Calculation

          (this exact pill on a view which calculates the script formula).

           

          So in your particular case it would be one vector of the size of 7,

          or even the two vectors of the sizes 3 and 4 (if Compute using -> Cell).

           

          To cope with this, one would have to generate somehow

          an additional Marks on a view -- up to a total of 7 in your case --

          from the two original Dimension Values.

           

          So the (seemingly simple) problem quickly becomes complicated.

           

          A common approach is to transform your data prior to Tableau --

          using a tool which does a Split & (Un)Pivot transformation(s).

           

          Besides, Tableau has both ones -- the SPLIT() function and the Pivot option --

          but the order of execution can not be 'Split first then Pivot'

          (in fact it could be in reverse, because of the internal Order of Operations).

           

          Another approach -- which helps in a particular case of finite word counts --

          is to generate the 'Scaffold' rows for the Marks right in the datasource --

          typically using a Join with a sequence of integers (as a table of course).

          Then use Tableau (Row-Level) string calculations to fill those Marks on a view.

           

          Please find the attached as an example of the latter.

          Hope it could help a bit.

           

          Yours,

          Yuri

          1 of 1 people found this helpful
          • 2. Re: Problem with R strsplit - 'unexpected type' error
            James Martin

            Hi Yuri,

             

            Thanks so much for the detailed reply - the final portion and attached workbook are the exact solution we're looking for.

             

            The only problem is, I see on your join you used a Join Calculation of '1' to join them.  However, our client uses version 10.1 and this functionality is not available until 10.2.

             

            Is there an alternative way to join the ID table, or will the attached solution only work for us in 10.2 and greater?

             

            Thanks!

            • 3. Re: Problem with R strsplit - 'unexpected type' error
              Yuriy Fal

              HI James,

               

              Glad to help.

               

              The old-school (pre-10.2) method of a cross-join

              is to have a column in each table with the value of 1

              and join on it.

               

              Yours,

              Yuri

              • 4. Re: Problem with R strsplit - 'unexpected type' error
                James Martin

                Thanks Yuri.

                 

                I think we will be able to do that.  The only issue with the above solution is that it creates duplicate counts and skews the overall numbers.  For that reason I'm creating a second connection to the same data source, for this individual view.  That way it doesn't inflate any numbers in the base data. 

                 

                Let me know if you have any other advice on that, but otherwise thanks very much - very helpful!

                • 5. Re: Problem with R strsplit - 'unexpected type' error
                  Yuriy Fal

                  James, you're welcome.

                   

                  My ultimate advice:

                  If it works, let it work.

                   

                  Yours,

                  Yuri