4 Replies Latest reply: Apr 18, 2017 8:49 AM by Christina Galante RSS

    Separate

    Christina Galante

      I have a field in a table that lists personnel and their roles associated with a project. Each person always has their role identified to the right of their name. Commas always separate the personnel. The naming convention for roles is always the same (Leader, Assistant, Analyst), but the number of people assigned to each project is not, and there may not always be someone assigned to a particular role yet. Additionally, the roles are not listed in any particular order.

      Example:

      ProjectPersonnel
      Project 1Danny Peterson Leader, Hannah Moore Assistant, Katie Evans Analyst
      Project 2Paige Kensington Leader, Eric Dalton Analyst, Arnold Stevens Assistant, Beth Moore Analyst
      Project 3Sarah Kent Assistant, Tyler Gorman Leader, John Rossi Analyst
      Project 4Anne Stone Assistant

       

      I am looking for a way to create a column to identify just the Analysts.

      Example:

      ProjectAnalyst
      Project 1Katie Evans
      Project 2Eric Dalton
      Project 2Beth Moore
      Project 3John Rossi
      Project 4

       

      How can I go about doing this, without altering the source data?

        • Re: Separate
          Aar Kay

          if you do not wish to altering the source data then use resident load from the main table then create a secondary table and then link to primary table

          Table1:

          LOAD * INLINE [

          Project     Personnel

          Project 1     Danny Peterson Leader, Hannah Moore Assistant, Katie Evans Analyst

          Project 2     Paige Kensington Leader, Eric Dalton Analyst, Arnold Stevens Assistant, Beth Moore Analyst

          Project 3     Sarah Kent Assistant, Tyler Gorman Leader, John Rossi Analyst

          Project 4     Anne Stone Assistant

          ](delimiter IS '     ');

          Table2:

          Load

                Project,

               TextBetween(temp,' ','Analyst') as Analyst

          where

                Not IsNull(TextBetween(temp,' ','Analyst')) ;

          Load Project,             

          Subfield(Personnel,',') as temp

          Resident

               Table1;

            • Re: Separate
              Christina Galante

              This is great! Although it is only working when the Analyst is not listed as the first person in the Personnel column. When the Analyst is the first person, only his or her last name appears in the new column, rather than the full name. Any ideas on how to fix that?

                • Re: Separate
                  Tresesco B

                  May be like:

                   

                  LOAD *,

                         If(Index(Personnel,'Analyst'),        Left(Trim(SubField(Personnel,',',-1)),Index(Trim(SubField(Personnel,',',-1)),'Analyst')-1)) as Analyst

                  INLINE [

                  Project Personnel

                  Project1 Danny Peterson Leader, Hannah Moore Assistant, Katie Evans Analyst

                  Project2 Paige Kensington Leader, Eric Dalton Analyst, Arnold Stevens Assistant, Beth Moore Analyst

                  Project3 Sarah Kent Assistant, Tyler Gorman Leader, John Rossi Analyst

                  Project4 Anne Stone Assistant

                  ](delimiter is ' ' );

                  • Re: Separate
                    Christina Galante

                    Actually, I got this to work by changing it to the following:

                    Subfield(', '&Personnel,',') as temp

                    Thanks!