3 Replies Latest reply on May 25, 2020 5:19 PM by shuhei saito

    Why can't I split values using brackets in Tableau Prep?

    Patricia B

      Hi there,

       

      I am new to Tableau Prep and I was wondering if anyone can help me please. I have 2 data sets for music sales.

       

      Data set 1 is my big data set which includes song titles, sales of song titles, revenue, profit, etc. The second data set includes song titles, genre, singer, etc. I need to add genre and singer to data set 1. So I want to use a left join, using the common variable "song titles" however the data in both sheets are very messy (aka the song titles look very different). I tried to do custom splits using brackets but it isn't working. Can anyone please advise me the best way to clean this?

       

      This is some mock data:

       

      Data set 1:

       

      Song TitleSales 2017
      price per song
      summer fun (ii)/Summertime Fun33420.99
      the darkness/darkness (iii)453450.78
      chill zone/ chill zone (ii)3450.66
      the yes no (v)440.33

       

      Data set 2:  

      Song TitleGenreSinger
      summer funRockLea Ja
      darknessPopVeee
      chill zonePopMarquees
      yes no, thePopThe Rocks

       

      Data set 1 is approx 500,000 lines.

       

      My initial response is to clean song titles in Data Set 1 before any joins by splitting the values on "/" and taking the fields before it (e.g. from "summer fun (ii)/summertime fun" --> "summer fun (ii)")

      However, I then tried to split it on the brackets "(" to remove the "(ii)" but nothing happens on Tableau Prep when I try to split this. Would anyone be able to tell me why?

       

       

       

      Also if anyone has any ideas on how to get around the issue in Data Set 1 with the "the" at the end (e.g. yes no, the) as opposed to the actual title (e.g. the yes no) please let me now!

       

      Any help would be super useful. Thank you!