Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
wjtam206
Contributor
Contributor

Trim URL string

Hello,

I have a question trimming a URL string in qlik.

For example,

Original URLDesired URL Output
https://www.affordablecollegesonline.org/degrees/mba-programs/#Types-of-Online-MBAsaffordablecollegesonline.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!

1 Solution

Accepted Solutions
johncaqc
Specialist
Specialist

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

View solution in original post

8 Replies
andrey_krylov
Specialist
Specialist

Hi, Wesley. Maybe something like this

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

wjtam206
Contributor
Contributor

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

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

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

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

This is output from using your formula earlier,

Original URLOutput
https://www.affordablecollegesonline.org/degrees/mba-programs/#Types-of-Online-MBAsaffordablecollegesonline.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
Specialist
Specialist

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

View solution in original post

wjtam206
Contributor
Contributor

It works now. Thank you!