Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
orangebloss
Creator
Creator

Splitting a cell then concatenating by specified tex

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

Labels (2)
6 Replies
Vegar
MVP
MVP

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
Creator
Creator
Author

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

Try this

Vegar_0-1696942639049.png

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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;