topic extracting a code(of variable length) after a character and before a string in QlikView

extracting a code(of variable length) after a character and before a string

Thu, 10 Apr 2014 23:23:55 GMT

I have URLs like the following that I need to extract the code after the last '/' and before the '-4.html' in the load script

/history/2/52557-4.html -> want to return 52557

/abc/xyz/11/3-4.html -> want to return 3

I tried: TextBetween(MyString,'/','-4.html') and it's returning the text between the first '/' rather than the last.

I also tried: TextBetween(right(MyString,15),'/','-4.html'), but since the code can be variable length, I'm not sure how to not capture other preceding '/' with the right() function.

Appreciate the help.

Thanks.

-Ming

Re: extracting a code(of variable length) after a character and before a string

disqr_rm — Fri, 11 Apr 2014 01:07:30 GMT

Hi Ming,

Following should do the trick:

TextBetween(URL, '/', '-4', SubStringCount(URL, '/'))

Re: extracting a code(of variable length) after a character and before a string

Fri, 11 Apr 2014 04:32:57 GMT

Thanks Rakesh, that worked perfectly.