Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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..
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
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.
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
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?
No, that's Stefan Walther's blog.
Have a nice week,
Stefan
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