2 Replies Latest reply on Dec 26, 2016 8:01 AM by Imran Dossa

    Extracting Values From Text String Then Group

    Imran Dossa

      Hi,

       

      I have an excel file containing survey responses from customers. The data looks similar to the table below:

       

      CustomerIDSurveyQuestions
      100001Q1;Y;Q2;N;Q3;Y;Q4;N;Q5;Y
      100002Q1;N;Q3;N;Q5;N
      100003Q1;Y;Q3;N;Q4;Y
      100004Q3;Y;Q4;N
      100005Q1;N

       

      I'd like to to create a count of each question and whether the customer has responded with a yes or no, similar to the table below:

       

       

      QuestionsYesNo
      Q122
      Q201
      Q322
      Q412
      Q511

       

      I was able to use the FIND command to determine whether question 1 was a yes or no, but this would require creating 25 separate calculations (because we have 25 possible questions that a customer can receive; questions received by the customer are randomized). Is there an easier way to determine the count for each question? My other thought was to use a value list, but not sure how to join the two sheets. Any help would be greatly appreciated. I have attached the Tableau file as a sample.

       

      Thanks,

       

      Imran.