Match Comma delimited values

[Qliklearner3]

Hi I've a column with comma delimited values like below, I want to check each row one by one and match each delimited value, if all the delimited value in a row have either 'a' or 'b' in them then it's a match else unmated as newcolumn. How Can I achieve this ?  Any help is appreciated.

eg- Table 1

         Name

         agh,Jcg, bit,akl    - unmatched as one value has neither 'a' or 'b' in it

         tcm,edf,jkl - unmatched

         abc,dab,tah - match as all the values have 'a' in it

         bcg,hap,dbn,mah - match as all the values have either 'a' or 'b' in it

QlikView 

Thank

[abhijitnalekar]

Hi @Qliklearner3 ,

As a result, you want to show the string or just want to create a flag?

like   in bcg,hap,dbn,mah - match as all the values have either 'a' or 'b' in it in this case you want to show "bcg,hap,mah" as a result or just "matched" string.

[RsQK · Accepted solution]

Hi, this might be it:

temp_data:
LOAD *, ROWNO() AS row INLINE [
Name
agh,Jcg, bit,akl
tcm,edf,jkl
abc,dab,tah
bcg,hap,dbn,mah
] (DELIMITER IS ';');

temp_values:
LOAD DISTINCT
Name
RESIDENT temp_data;

temp_values2:
LOAD *,
IF(WILDMATCH(name_subfield,'*a*','*b*'),1,0) as match_flag;

LOAD
Name,
SUBFIELD(Name,',') AS name_subfield
RESIDENT temp_values;

LEFT JOIN (temp_values)
LOAD
Name,
SUM(match_flag) AS match_count,
COUNT(name_subfield) AS part_count
RESIDENT temp_values2
GROUP BY Name;

DROP TABLE temp_values2;

LEFT JOIN (temp_data)
LOAD
Name,
IF(match_count=part_count,'Match','Unmatched') as new_column
RESIDENT temp_values;

DROP TABLE temp_values;

[Qliklearner3]

I want to create a flag with two values matched and unmatched.

[Qliklearner3]

Thanks @RsQK that works !

[MarcoWedel]

another solution might be:

mapPurgeMultipleA:
Mapping LOAD Repeat('a',1000-RecNo()), 'a' AutoGenerate 998;

table1:
LOAD *, If(SubStringCount(MapSubString('mapPurgeMultipleA',Replace(KeepChar(Name,'ab,'),'b','a')),'a')=SubStringCount(Name,',')+1,'matched','unmatched')	as Flag
Inline [
    Name
    agh,Jcg,bit,akl
    tcm,edf,jkl
    abc,dab,tah
    bcg,hap,dbn,mah
    aaa,bbb,aaa,bbb,aaa,bbb
    aaa,bbb,aaa,bbb,aaa,ccc
    ab,ab,ba,ba,ba
    abc,abc,cba,cba,cba
    a,a,a,a,a,a,a,a,a,b
    a,b,c,d,e,f
    cde,def,efg,fgh   
] (delimiter is spaces);

hope this helps

Marco