1 Reply Latest reply on Jun 2, 2016 1:33 PM by Joe Oppelt

    Delineating fields with multiple choice combinations

    Karen Hecox

      Hi everyone,

      I'm encountering an issue with data and how to properly delineate fields so that the split columns contain the same information.  I know that, given an infinite amount of time, I can import the data to excel first, split it there, and move the data into the columns I'm looking for (however, this would take literal days due to the number of records and number of reports).  I'm hoping there is a script/formula I can use in Tableau that I can use to create the splits/columns I'm looking for.

       

      One of the WA-State databases we pull our data from collects multiple choice information from uses.  In this instance, it asks them which kind of childcare they are looking for, and allows them to select more than one response. When we bring the data into Tableau we get a field that says, "Type of care" and the results have each kind of care the parents were looking for in a string separated by commas.  I am able to split this column into multiple columns splitting at commas, however, the issue is that the new columns are differentiated by the number of choices they made, not by the actual choices,  (and what I'd really like to see is the number of instances of each choice).

        For example:

       

      Participant 1:  childcare center, family care, preschool program

      Participant 2: childcare center, preschool program, some Saturdays

      Participant 3: family care, school age program, some Saturdays, some Sundays

       

      When I split these, I get the following columns:

       

      Split 1

      Childcare center

      Childcare center

      family care

       

      Split 2

      Family care

      Preschool program

      school age program

       

      Split 3

      preschool program

      some Saturdays

      Some Saturdays

       

      Split 4

      null

      null

      Some Sundays. 

       

       

      What I would like to get is this:

       

      Split 1

      Child care center:  count: 2

       

       

      Split 2

      family care :2

       

       

      Split 3

      preschool program: 2

       

       

      Split 4

       

      school age program: 1

       

      Split 5

      Some Saturdays: 2

       

      Split 6

       

      Some Sundays: 2

       

       

       

       

      If there is no way for me to change how the data is collected, is there a way in Tableau for me to create these splits (and therefore counts)  without going into excel first to edit the data? I've attached a pic of how it currently looks,and a diagram of how I'd eventually like my report to look. 

       

      Thanks,
      KH