2 Replies Latest reply on Aug 5, 2018 12:33 PM by Tom Hier

    Separating Multiple Entries in a Single Data Field and Creating New Records -- Can Tableau Prep Handle This?

    Tom Hier

      Hi.  I'm just starting to use Tableau Prep and am wondering whether it can handle a data issue that I encounter frequently.  Below is the issue.

       

      I get class schedule data files in Excel formats from universities for utilization analyses that I undertake.  Ideally, each record in the database should be one unique record -- i.e., a class that meets at a given time in a given room.  Often, however, a class may be scheduled in two or three rooms simultaneously (e.g., it starts in the lecture hall, then moves to a lab, or another space).  University staff creating the data set may provide only one record for the course, with all the rooms in one field (called "Room").  I cannot analyze data with that structure because I have to analyze utilization for each room separately.

       

      Example:  Math001 meets on Mondays from 10am to 11am, and say it meets in three rooms -- Smith001, Jones005 and Johnson212.  The Excel database shows one record, and the "Room" field includes the following:  Smith001, Jones005, Johnson212.  Instead of one record for Math001, with three room names in the Room field, I need to create three records for Math001, where each Room entry has only one room name.

       

      Is this possible using Tableau Prep?  Or is there another way that I might achieve this.  I have only the most basic knowledge of SQL and am not a programmer, so I'm looking for solutions that do not require heavy programming.

       

      I've included an Excel file with two sample datasets.   The first is what I might get from a client.  The second is what I need it to look like to be able to analyze it.

       

      Thanks.