topic Re: Match Comma delimited values in App Development

Match Comma delimited values

Qliklearner3 — Sat, 16 Nov 2024 16:53:04 GMT

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

Thank

Re: Match Comma delimited values

abhijitnalekar — Thu, 16 Dec 2021 08:31:48 GMT

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.

Re: Match Comma delimited values

RsQK — Thu, 16 Dec 2021 09:00:32 GMT

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;

Re: Match Comma delimited values

Qliklearner3 — Thu, 16 Dec 2021 10:56:15 GMT

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

Re: Match Comma delimited values

Qliklearner3 — Thu, 16 Dec 2021 10:59:15 GMT

Thanks @RsQK that works !

Re: Match Comma delimited values

MarcoWedel — Thu, 16 Dec 2021 20:27:24 GMT

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