Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Abhishekb
Contributor III
Contributor III

how to Extract just repeated capital letters from string.

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

Labels (3)
2 Solutions

Accepted Solutions
MarcoWedel

maybe helpful:

MarcoWedel_1-1698786784003.png

 

 

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
];

 

 

View solution in original post

marcus_sommer

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

View solution in original post

3 Replies
MarcoWedel

maybe helpful:

MarcoWedel_1-1698786784003.png

 

 

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
];

 

 

Abhishekb
Contributor III
Contributor III
Author

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.

marcus_sommer

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