Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Substring from text field from variable start position

Hi all,

In my script I need to extract from an excel field a substring that begins with the letters "PT" and is followed by 8 numeric characters. The source text field from which to extract the substring can have multiple occurrences of substring "PT....." but only one followed by 8 numeric characters.

Let me explain with an example:

source text string: "Support Automation    PT7aW. Resolution  PT8. Related Issues Activities PT63004910 Date% Event Log      11. Attachments"

The only string to extract is "PT63004910" and not "PT7aW" or "PT8".

Thanks for the help.

16 Replies
Not applicable
Author

Hi Stefan,

Thanks for you help stefan.

You are right! I think this is to difficult to extract a city name. What I wanted to do is first select text between to words in this case:

Trim(Capitalize(TextBetween(Text, 'Deliver To', 'Customer',1))) as DestinationTest,

Then use something like:

=Pick(Wildmatch(DestinationTest, *City*), City)

City in this case in not a string but a fieldname that contains all the cities. I wanted to use the wildmatch function to extract the City that matches the field City.


So if the field City, contains ''Casablcana" that matches in the DestinationTest, it would pick that City name.

I tried it but it doesn't work.

But thanks for all your help!!! I really appriciate it.

I wish they had the city names in the database, it would have been much easier..

swuehl
MVP
MVP

I don't think that you can use a field in Wildmatch (well, no, you probably could using a concat technique, but let's keep that for a later time).

You could probably use a mapping table together with mapsubstring function to search and map your city names in case you have all citynames available:

CITYMAP:

MAPPING LOAD * INLINE [

F1, F2

ALMATY, §1Almaty§2

CASABLANCA, §1Casablanca§2

];

...

Destination:

Load *,

TextBetween(MapSubString('CITYMAP',DestTest1),'§1','§2') as DestTest1Result;

Load

*,

Trim(Capitalize(TextBetween(Text,pick(WildMatch(Text, '*CIP*'), 'CIP'), pick(wildmatch(Text, '*HS*', '*84*', '*TARIFF*'), 'HS', '84', 'TARIFF', )))) as Destination,

Trim(UPPER(TextBetween(Text, 'Deliver To', 'Customer',1))) as DestTest1,

TextBetween(Text, pick(WildMatch(Text, '*Deliver To:*'), 'Deliver To:'), pick(WildMatch(Text,'*Page 1*'), 'Page 1')) as TestDest

...

The Citymap mapping table could be loaded from any source, read the city field one time in in UPPER case, one time concatenated with the two delimiters (which are arbitrary).

See also attached.

Regards,

Stefan

Not applicable
Author

Hi Stefan,

Sorry for the late response. Thanks for your help! It worked with some City's with others not. There were numbers and text after the cityname. We've decived to fill The cityname manualy in Excel.

Thanks for your help! I've learned alot from this experience .

Carter.

swuehl
MVP
MVP

Well, if you look at my example I attached at my last post, I think this handled numbers or text after the cityname quite well.

But anyway, since you have coped with it in another way, I am glad that your problem seemed to be solved so far.

Have fun,

Stefan

Not applicable
Author

Hi Stefan,

I recheck the qvw file and did a reload of all the orders. You were right!!! It did extract the destination. I accently looked at my expression, which showed the numbers and text after the city name. I apologize for not looking correctly!

Again, thanks for all your help . I really appriciate it.

Is www.qlikblog.at your website?

swuehl
MVP
MVP

No, that's Stefan Walther's blog.

Have a nice week,

Stefan

qliksus
Specialist II
Specialist II

Hi ,

Try this approach if you dont want to use macro

test1:
load SubStringCount( replace(KeepChar(a,'PT0123456789')&'-','PT','-PT'),'-') as new1 ;
LOAD * INLINE [
a
Support Automation    PT7aW. Resolution  PT8. Related Issues Activities PT63004910 Date% Event Log      11. Attachments ];

let loopval = peek('new1',0,'test1') ;

test:
LOAD * INLINE [
a
Support Automation    PT7aW. Resolution  PT8. Related Issues Activities PT63004910 Date% Event Log      11. Attachments ];


for i = 0 to $(loopval)

load

left(if ( len(textbetween( replace(KeepChar(a,'PT0123456789')&'-','PT','-PT'),'-','-' ,$(i)))>=10 ,

textbetween( replace(KeepChar(a,'PT0123456789')&'-','PT','-PT'),'-','-' ,$(i)) ),10) as new
 
resident test ;

NEXT