Separate

[#1]

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:

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

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

Example:

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

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

[aarkay29 · Accepted solution]

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;

[#3]

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?

[tresesco]

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 ' ' );

[#5]

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

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

Thanks!