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).
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:
school age program
What I would like to get is this:
Child care center: count: 2
family care :2
preschool program: 2
school age program: 1
Some Saturdays: 2
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.
what I see in tableau.pub.zip 299.1 KB