topic Re: Separate in QlikView

Separate

Mon, 17 Apr 2017 18:18:37 GMT

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?

Re: Separate

aarkay29 — Mon, 17 Apr 2017 20:17:34 GMT

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

Tue, 18 Apr 2017 12:05:48 GMT

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

tresB — Tue, 18 Apr 2017 12:21:37 GMT

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

Tue, 18 Apr 2017 12:49:25 GMT

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

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

Thanks!