4 Replies Latest reply on Nov 6, 2018 7:11 AM by Jennifer VonHagel

    Splitting a text string with school grades (PK,K,1,2, etc.)

    Matthew Ingmire

      I'm new! First dataset (NYC public school data (mostly location-related data)) and first post:

       

      As you will see in the attached image, the "grade" column contains a text string of the grade levels taught at each school (PK,K,1,2,3,...SE.) I would like to manipulate the data based on grade levels. I assume that I need a column for each grade and some value in the appropriate column if the school teaches that level. I figured out how to split the text string, but I don't think this is going to help me. Apart from only the first 10 values being split, not all schools teach the same levels or the same number of levels.  I could do this in Excel, but I'm hoping there is a way to do this in Tableau (without having to change the dataset.)

       

      Thanks in advance for your help.

        • 1. Re: Splitting a text string with school grades (PK,K,1,2, etc.)
          Ombir Rathee

          Please attach a sample workbook and your expected output which you're able to achieve in excel so that it will be easier for us to help.

          • 2. Re: Splitting a text string with school grades (PK,K,1,2, etc.)
            Jennifer VonHagel

            Hi Matthew, congrats on your first dataset!

             

            Depending on the rest of your data and what you plan to do with it, it's hard to say if a bunch of columns is a good way to go, but you certainly can do that in Tableau if you wish.

             

            If the strings are consistent throughout, you can create a bunch of columns with True/False (or 1/0) flags for whether each school has that grade. So if grade 1 is always "01" (and not just "1"), you could create columns like the following. This will look in your "Grades" column for the string 'PK', for example, and return TRUE if it finds it and FALSE if it doesn't.

             

            PK: CONTAINS([Grades],'PK')

            0K: CONTAINS([Grades],'0K')

            01: CONTAINS([Grades],'01')

            ... more columns

             

            In Tableau, in the left pane, go to Dimensions, click the arrow, and choose Calculated Field

            Write your calculation and click OK.

            PK will show as a field in the Dimensions pane. You can right click it, duplicate it, and use that to write your next column's formula.

            - - - - - - - - - - - - -

             

            Or if you prefer 1s and 0s:

            PK: IF CONTAINS([Grades],'PK') THEN 1 ELSE 0 END

             

            Or if you would like cells to be NULL if the grade does not apply:

            PK: IF CONTAINS([Grades],'PK') THEN 'whatever you want here' END

             

            I hope this helps. If you find columns aren't letting you visualize what you want, let us know. I have a feeling you may need to pivot the columns to be rows so you can filter and compare by them, but again, not sure what the rest of your data set looks like or what you plan to do with it so columns might be fine.

             

            Best,

            Jennifer

            1 of 1 people found this helpful
            • 3. Re: Splitting a text string with school grades (PK,K,1,2, etc.)
              Matthew Ingmire

              Thank you Jennifer!  I marked your reply as helpful!

              I have a few follow-up questions.  To give you some context, I am working with this data set for two reasons:

              1. I want to learn how to use Tableau,
              2. I am considering substitute teaching and want to an idea of which high schools and middle schools are in certain neighborhoods.

               

              I followed your suggestion and created a calculated field (CONTAIN([Grade]),"12") which worked, but then I realized I would need to check if the string only contained a specific grade level, if I want to filter by individual grade levels.

               

              For my immediate purposes, I will write two calculated fields - one to check if the school teaches grade 12 (for high school levels), and another to check if the school teaches grade 7 (for middle school levels.) 

               

              So far I am using two datasets (that I joined by zip code fields (yay me!))  One contains school administrative and location information; the other contains NYC borough and neighborhood information.  If I want to plot schools by their street addresses, I believe I will need to use a service like Alteryx to look up lat/long. I am trying to figure that today. 

               

              I don't want to take up too much of your time, but I'd be grateful for any thoughts and suggestions you may have.  Is there a way for me to upload my Tableau work so you can see it?

              1 of 1 people found this helpful
              • 4. Re: Splitting a text string with school grades (PK,K,1,2, etc.)
                Jennifer VonHagel

                Hi Matthew,

                 

                You're welcome! Sure, you can upload your workbook; it's a best practice in the forum to upload a packaged workbook (.twbx) with your question so folks can give specific help.  To upload:

                 

                1. You have to open this thread in the forum, not be in it in your Inbox. So if you're in your Inbox, click on the title of the thread and that will open it.

                 

                2. Once you're there, hit reply to a message, and you will have in the header a link to an Advanced Editor where you can upload a workbook.

                 

                Best,

                Jennifer