Splitting a cell then concatenating by specified tex

[orangebloss]

I have a field (Certs List) that contains multiple pieces of information. 

CertABC1,CertDEF1,CertABC2

I want to split this information out into columns ABC and DEF , grouping together all the ABC and DEF certs:

ABC	DEF
CertABC1,CertABC2	CertDEF

Is this even possible? If it is how to achieve it?

Many thanks

[Vegar]

What else can you tell us about CertList?

• Are there a limited number of unique ABC and DEF certs?
• Are there a fixed or maximum number of certs per record?

If there is some limitations then you could get away with some logical tests if(...) & if(...) etc.

If not then you could do a two step approach.

1. Split all the values into individual cert values

2. Load the individual cert values with desired logic. 

If(...) as ABC,

IF(...) AS DEF

[orangebloss]

There is no limit to the number of unique certs and no limit to the max number of certs per record. However the certs are limited to being ABC,DEF or GHI so I was thinking wildmatch or Like AS to allocate the columns which I already use to check if the values exist in the current field

if(WildMatch(CertList,'*ABC*'),'Y','N') as "ABC Cert",
if(WildMatch(CertList,'*DEF*'),'Y','N') as "DEF Cert",
if(WildMatch(CertList,'*GHI*'),'Y','N') as "GHI Cert",

A cert may appear more than once in the data as they are associated with one or more projects:

Project	Certs
1	CertABC1,CertDEF1,CertABC2
2	CertDEF1
3	CertGHI1,CertABC2
4
5	CertABC3

So the desired outcome for the above is

Project	ABC	DEF	GHI
1	CertABC1,CertABC2	CertDEF1
2		CertDEF1
3	CertABC2		CertGHI1
4
5	CertABC3

I've split the CertList field up into a table using Subfield(CertList,',') as Certificate Name it's just how to merge them back into the one column by Certificate type (ABD, DEF, GHI)

[Vegar]

Try this

Project:
LOAD * Inline [
Project	Certs
1	CertABC1,CertDEF1,CertABC2
2	CertDEF1
3	CertGHI1,CertABC2
4	
5	CertABC3
] (delimiter is '	');


for each _certs in FieldValueList('Certs')
Certs:
Load 
	'$(_certs)' as Certs,
	SubField('$(_certs)', ',') as Cert
AutoGenerate 1
;	

NEXT

For each _type in 'ABC', 'DEF', 'GHI'

	Left join (Project)
	LOAD 
		Certs,
		Concat(Cert,',') as [Cert$(_type)]
	Resident 
		Certs
	where 
	WildMatch(Cert, '*$(_type)*')>0
	group by Certs
	;
next

drop table Certs;	

[orangebloss]

Thanks - potentially daft question but I'm loading the data from a QVD file - where would this appear in the above? Does it just replace the load inline?

LOAD

   [Item_ID] as "CertItem ID",

[Level2_Parent] as "Project",

    [Level2_Parent]&'-'&[Item_ID] as Ljoin,

    SubField(Certs,',') As CertificateName,

from

FROM [lib://Engineering Dev:DataFiles/Engineering_LongLeadv3.qvd]
(qvd);

[orangebloss]

I feel I'm almost there! Some syntax changes  but now as below  - I'm just not managing to get it to put it into the correct columns.  The ABC/DEF/GHI could appear anywhere in the Cert name - I'm guessing it's the Wildmatch that may need adjusting?

LOAD
  [Item_ID] as "CertItem ID2",
[Level2_Parent] as "CertContract2",
   [Level2_Parent]&'-'&[Item_ID] as Ljoin,
    


  
    
Certificates as Certs
FROM [lib://Engineering Dev:DataFiles/Engineering_LongLeadv3.qvd](qvd);


for each _certs in Certificates

Certs:
Load 

'$(_certs)' as Certs,
SubField('$(_certs)', ',') as Cert
AutoGenerate 1

;

NEXT

For each _type in 'ABC', 'DEF', 'GHI'
//Left join ()

LOAD 
Certs,
Concat(Cert,',') as [Cert$(_type)]
Resident 
Certs
where 
WildMatch(Cert, '*$(_type)*')>0
  
group by Certs
;
next;


drop table Certs;

[orangebloss]

Solved!

LOAD
  [Item_ID] as "CertItem ID2",
	[Level2_Parent] as "CertContract2",
   [Level2_Parent]&'-'&[Item_ID] as Ljoin,
    
  	
	
  
    
Certificates as Certificates1
FROM [lib://Engineering Dev:DataFiles/Engineering_LongLeadv3.qvd](qvd);


for each _certs in  FieldValueList('Certificates')

Certs:
Load 
 
	'$(_certs)' as Certificates1,
	SubField('$(_certs)', ',') as Cert
AutoGenerate 1

;	

NEXT

For each _type in 'CAP', 'EMC', 'FLM'
//Left join ( )
	
	LOAD 
		Certificates1,
		Concat(Cert,',') as [Cert$(_type)]
	Resident 
		Certs
	where 
	WildMatch(Cert, '*$(_type)*')>0
 
  
	group by Certificates1
	;
next


drop table Certs;