Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Separate

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?

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

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;

View solution in original post

4 Replies
aarkay29
Specialist
Specialist

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;

Not applicable
Author

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
MVP
MVP

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

Not applicable
Author

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

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

Thanks!