Logic help

[raju_salmon]

Hi,

I would need to create a new filed on top of existing one with a logic where it should return only the string which contains FAQ. I have tried using wildmatch, replace etc but did not give the below expected result. In the below example, max FAQ'a in a cell is 2 however in my data, many FAQ strings are available in a single cell with , separated.

Actual Field	Expected Field
ID,BM FAQ,Capture and Route	BM FAQ
ID,BM FAQ,PW Reset,Capture and Route	BM FAQ
ID,CSBG FAQ,Passcode,Capture and Route	CSBG FAQ
ID,CSBG FAQ,Capture and Route	CSBG FAQ
ID,CSBG FAQ,PW Reset,Capture and Route	CSBG FAQ
ID,Alerts,Passcode,BM FAQ,Capture and Route	BM FAQ
ID,Alerts,Passcode,CSBG FAQ,Capture and Route	CSBG FAQ
ID,Alerts,Passcode,PRCTD FAQ,Capture and Route	PRCTD FAQ
ID,Alerts,Passcode,Capture and Route, PRCTD FAQ,Steps FAQ	PRCTD FAQ,Steps FAQ
ID,Capture,Passcode,Invalid credit alert,PRCTD FAQ,Capture and Route, Steps FAQ	PRCTD FAQ,Steps FAQ
ID,Capture,Passcode,Valid credit alert,PRCTD FAQ,Steps FAQ	PRCTD FAQ,Steps FAQ

Thanks for you help in advance.

[jpenuliar · Accepted solution]

see below:

Temp00:
Load * Inline [
Actual Field
'ID,BM FAQ,Capture and Route'
'ID,BM FAQ,PW Reset,Capture and Route'
'ID,CSBG FAQ,Passcode,Capture and Route'
'ID,CSBG FAQ,Capture and Route'
'ID,CSBG FAQ,PW Reset,Capture and Route'
'ID,Alerts,Passcode,BM FAQ,Capture and Route'
'ID,Alerts,Passcode,CSBG FAQ,Capture and Route'
'ID,Alerts,Passcode,PRCTD FAQ,Capture and Route'
'ID,Alerts,Passcode,Capture and Route, PRCTD FAQ,Steps FAQ'
'ID,Capture,Passcode,Invalid credit alert,PRCTD FAQ,Capture and Route, Steps FAQ'
'ID,Capture,Passcode,Valid credit alert,PRCTD FAQ,Steps FAQ'
];

Test:
Load
"Actual Field",
Concat("Expected Field",',') as "Expected Field"
Where flg =1
Group by "Actual Field"
;
Load
Rec,
"Actual Field",
"Expected Field",
If(WildMatch("Expected Field",'*FAQ*'),1,0) as flg
;
Load
RecNo() as Rec,
"Actual Field",
Subfield("Actual Field",',') as 'Expected Field'
Resident Temp00
;

[raju_salmon]

Excellent. Thank you.