QlikView App Dev

[wjtam206]

Hello,

I have a question trimming a URL string in qlik.

For example,

Original URL	Desired URL Output
https://www.affordablecollegesonline.org/degrees/mba-programs/#Types-of-Online-MBAs	affordablecollegesonline.org/degrees/mba-programs
https://www.topmanagementdegrees.com/rankings/cheapest-online-mba/	topmanagementdegrees.com/rankings/cheapest-online-mba

I want to remove  'https://www.' and the last '/' in the string. Also, if they have '/#' in the string then remove everything after the '/#'. Any suggestions?

Thanks!

[andrey_krylov]

Hi, Wesley. Maybe something like this

SubField(Replace(If(Right(UrlString, 1) = '/', Left(UrlString, Len(UrlString)-1), UrlString), 'https://www.', ''), '/#', 1)

[wjtam206]

Hi Andrey,

It worked for the most part. What if there are urls starting with 'http://www.' instead? Where would place it in the formula?

Thank you!

[johncaqc]

Maybe with this modification to Andrey's expression?

SubField(Replace(If(Right(SubField(UrlString,'://',2), 1) = '/', Left(SubField(UrlString,'://',2), Len(SubField(UrlString,'://',2))-1), SubField(UrlString,'://',2)), 'www.', ''), '/#', 1)

HTH,

John

[wjtam206]

Hi John,

This actually works too! However, there are some URL strings in my table that are clean and have the desired URL output already. They are returned as null so how do I account for them as well?

[johncaqc]

What do you mean by "clean and have the desired output already"?

Please show some more examples of these, what you have and desired output.

[wjtam206]

This is output from using your formula earlier,

Original URL	Output
https://www.affordablecollegesonline.org/degrees/mba-programs/#Types-of-Online-MBAs	affordablecollegesonline.org/degrees/mba-programs
https://www.topmanagementdegrees.com/rankings/cheapest-online-mba/	topmanagementdegrees.com/rankings/cheapest-online-mba
discoverbusiness.us/education/online-mba	- (Null)

It works perfectly for URLs that need to be trimmed, like the first two URLs in the table. However, there are specific cases in my table where the URL was parsed already in the original URL column and returning nulls using your formula.

I hope this helps clarify the issue. Thanks!

[johncaqc · Accepted solution]

Starting to get a little crude, but seems to work;

If(Len(SubField(UrlString,'://',2))<1,UrlString,SubField(Replace(If(Right(SubField(UrlString,'://',2), 1) = '/', Left(SubField(UrlString,'://',2), Len(SubField(UrlString,'://',2))-1), SubField(UrlString,'://',2)), 'www.', ''), '/#', 1))

HTH,

John

[wjtam206]

It works now. Thank you!