1 Reply Latest reply on Oct 8, 2018 2:46 PM by Patrick A Van Der Hyde

    Tableau connect to R (Error Unexpected number of results returned by SCRIPT function)

    Ratna Sari

      Dear All,

       

      I have requirement to split a cell (there are names separated by comas inside each cell) into different cells in the same column (adding rows, not adding more columns).  You can see in this image the initial data and what I would like to get:

      1.png

       

      We need it to be dynamic so each time we update the data, the Tableau file will update it too. If it is static the user would have to do it manually each time so it is not a good solution.

      The only way we have found at the moment is doing it using the R connection with Tableau. We have made an easy program in R to do it and it works perfectly in Rstudio but when we use that code in Tableau (it is the calculated field called “split”), it says we are not returning the correct number of cells even when we checked it in Rstudio and it returns exactly the number of cells Tableau is asking for. Here you can see the message we get:

      2.png

       

      This is the script of the calculated field we are using:

       

      SCRIPT_STR("

      products <- .arg1

      out <- rep('0',length(products))

      idx2<-1

      for (idx0 in 1:length(products)){

        auxStr<-products[idx0] 

        auxArr <-unlist(strsplit(auxStr,','))   

        for (idx1 in 1:length(auxArr)){    

          out[idx2]<-auxArr[idx1]

          idx2<-idx2+1   

        }

      #we add cells with ‘na’ until we complete the whole column  

      }

      for(idx3 in idx2:28){

        out[idx3]<-'na'

      }

      out

      ",

      ATTR([Components1])

      )

       

      And when we try to change the number of result to reduce it, it keeps saying the same so we don’t know how to solve this problem and we haven’t been able to find any other way to do it using only Tableau. In case it is because we can’t add new rows to the Tableau file, we created another column (longer so we can be sure the result won’t be longer than that column and we complete the empty cells with “na”) but it is not working any way.  Here you can see the data and the long column we made (we called it “try” )

      3.png

       

      Do you know what can we do to solve the problem or if there is any another way to split cells in more rows (it has to be dynamic)?

      Thank you so much.

       

       

      Attached is the sample workbook