5 Replies Latest reply on Feb 13, 2013 10:31 AM by Karen Satke

    Combine Multiple Columns into One??

    Karen Satke

      I have been struggling to work out how to combine three dimensions into one.  Example: a hospital has three nurse shifts.  The data set will show a name for Day, Mid, and Evening for each patient.

       

       

      Day                        Mid                        Evening                              Patient Number

      Tom                       Jenny                    Linda                                     001

      Jenny                    George                 Harry                                     002

      Linda                     Jenny                    George                                 003

       

       

      Each nurse is accountable for the patient regardless of
      the shift they were on.

       

       

      I tried a calc field this way:

       

      If [Day] = “Tom” then “Tom”

      ELSEIF [Mid] = “Tom” then “Tom”

      ELSEIF [Evening] = “Tom” then “Tom”

      ELSEIF [Day] = “Jenny” then “Jenny”

      ELSEIF [Mid] = “Jenny” then “Jenny”

      . . .

       

      The resulting “group” calculated field couldn’t double count the rows.  So Tom would get credit for patient 001, and Jenny and Linda would not.

       

      Next try:

       

      IF [Day] = “Tom” or [Mid] = “Tom” or [Evening] = “Tom” THEN “Tom”

      ELSEIF [Day] = “Jenny” or [Mid] = “Jenny” or [Evening] = “Jenny” THEN “Jenny”

      . . .

       

       

      This also didn’t work.

       

      I’ve thought about using the Contains Function but that would just return the same results.  I've thought about a parameter, but that doesn't seem to cover all 3 fields either.

       

      In the end, I need to filter/group by each name across all fields.

       

      Any suggestions?

       

      Thank you, Karen

        • 1. Re: Combine Multiple Columns into One??
          Mark Holtz

          Hi Karen,

           

          I don't think I understand. You are trying to assign a single Nurse to a Patient? Does the Day shift take priority? Can you create a picture of what you want to be able to do? Or mock it up in Excel?

          • 2. Re: Combine Multiple Columns into One??
            Karen Satke

            Hi Mark, Thanks for responding!

             

            What I would like to do is filter by a name and return all the cases the name worked on.  Looking at the table above, if I "filtered" by Jenny, I would return Patient Case 001, 002, and 003.  If I "filtered" by George I would only return Cases 002, and 003. 

             

            In other words, if Jenny touched the case, I need to see all those cases.  There isn't a priority with shifts.

             

            The idea is that I can easily pull out all the cases a nurse worked on and run their individual stats.  (without running 3 manual filters)  This is something an end user will need to do so the filter will need to be a simple dropdown. 

             

            I have considered making multiple calc fields that would pull out each nurse:

            Jenny Group

            IF [Day] = “Jenny” or [Mid] = “Jenny” or [Evening] = “Jenny” THEN "Jenny

             

            Tom Group

            IF [Day] = “Tom” or [Mid] = “Tom” or [Evening] = “Tom” THEN “Tom"

            But then I will be running a bunch of calculated groups and have no way to bring them together.

             

            If it makes a difference, this is running from a SQL Server.

             

            Let me know if that helps (or makes it worse).

             

            Thanks!

            • 3. Re: Combine Multiple Columns into One??
              Karen Satke

              tableau snapshot.png

              This is what I have been working with.  I have a table with dimensional data and then fields of measures.  Three of the columns contain names I need to filter across.  The same names are in all three fields and may be in one or all fields.

               

              This pic is my last attempt at a solution with a dropdown on the right to filter (unsuccessfully).

               

              Thanks!

              1 of 1 people found this helpful
              • 4. Re: Combine Multiple Columns into One??
                Mark Holtz

                OK. I think I'm a bit clearer on what you're after. I'm still uncertain whether you need to see all of the results at once, or if it will suffice to be able to check one nurse name at a time.  If you want to be able to check each nurse uniquely, this might be a solution:


                1) Create a parameter with a list of string values hard-coded as the nurse names you want to check.

                (The weakness of this solution is that Tableau does not allow you to have a parameter be dynamically populated from values in a field as the underlying data changes. If you ever had a new nurse name show up after an update, you'd have to manually add that name to the list of selectable Nurses.)
                2) Create a formula to run the parameter up against each Shift (Day, Mid, Evening):

                IF [Day] = [NurseNameParameter] THEN 'Show'

                ELSEIF [Mid] = [NurseNameParameter] THEN 'Show'

                ELSEIF [Evening] = [NurseNameParameter] THEN 'Show'

                ELSE 'Hide' END

                 

                For whichever NurseName you are checking, you could filter on that field to reduce the data to only those records where the name appears in at least one of those shifts.

                 

                If however, you need to be able to see all the Nurses at all times, the track you were on in your response is the way I'd go...Create a separate calculated field for each nurse and hard-code the nurse name in the formula (basically the same as I have above, but using 'Tom' THEN 'Show' in the [Tom] calc field instead of [NurseNameParameter] THEN 'Show'. You could build separate views each utilizing a different Nurse-Name field as the filter. Still, this method requires you to intervene manually if/when your data ever presents a new nurse...

                 

                Once you create the multiple nurse-name fields, you might be able to utilize sets to combine them into a single-filter... I haven't worked with sets very much, but here is a guide: http://onlinehelp.tableausoftware.com/current/pro/online/en-us/sortgroup_sets_examples_ex2uniqueencoding.html#id118PG0F0783

                 

                If that STILL doesn't get you where you'd like, then perhaps you could post your workbook. Cheers.


                • 5. Re: Combine Multiple Columns into One??
                  Karen Satke

                  Hi Mark,

                    Thank you so much!!!  I had a few data clean up issues to take care of first.  But after that your solution worked great!

                   

                  Thank you!!!

                  -Karen