topic Re: how to Extract just repeated capital letters from string. in QlikView

how to Extract just repeated capital letters from string.

Abhishekb — Wed, 11 Oct 2023 05:15:00 GMT

Hi community,

If I have the info let's say ***97ChennaiAAA2875/CA1220.

and if AAA is dept name in this. How can I just extract the dept name from above string.

Kindly resolve the above problem.

Thank you in advance,

Abhishekb

Re: how to Extract just repeated capital letters from string.

MarcoWedel — Tue, 31 Oct 2023 21:13:28 GMT

maybe helpful:

mapExtCapLett: Mapping LOAD Chr(RecNo()), '_' AutoGenerate 65535 Where not Index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',Chr(RecNo())); table1: LOAD * Where Len(dept)>1; LOAD *, SubField(MapSubString('mapExtCapLett',SomeText),'_') as dept Inline [ SomeText ***97ChennaiA2873/CA1220 ***97ChennaiAA2874/CA1220 ***97ChennaiAAA2875/CA1220 ***97ChennaiAAAA2876/CA1220 ***97ChennaiB2877/CA1220 ***97ChennaiBB2878/CA1220 ***97ChennaiBBB2879/CA1220 ***97ChennaiBBBB2880/CA1220 ***97ChennaiA2881/CA1220 ***97ChennaiAB2882/CA1220 ***97ChennaiABC2883/CA1220 ***97ChennaiABCD2884/CA1220 ];

Re: how to Extract just repeated capital letters from string.

Abhishekb — Fri, 03 Nov 2023 04:57:09 GMT

Hi @MarcoWedel ,

Thanks for answering the problem but this is not the type of solution I needed.

In ***97ChennaiAAA2875/CA1220. I need only AAA as output. I don't need all capital letters.

Re: how to Extract just repeated capital letters from string.

marcus_sommer — Fri, 03 Nov 2023 07:32:40 GMT

The suggestion from Marco will work. If you don't want to consider the entire string then just take the appropriate string-part, maybe by separating the part before and after the / with another subfield() in beforehand.

Further thoughts for extending the shown example might be to add a recno() and rowno() to the load as well as replacing the two-parameter subfield() loop with a three-parameter subfield() and controlling the loop per iterno() and while-statement. With it each record and string-part could be tracked - to be counted, filtered and/or aggregated - whatever is needed ...