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: 
Anonymous
Not applicable

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

11 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)

Anonymous
Not applicable
Author

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!

johnca
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

Anonymous
Not applicable
Author

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?

johnca
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.

Anonymous
Not applicable
Author

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!

johnca
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

Anonymous
Not applicable
Author

It works now. Thank you!

jblank
Contributor III
Contributor III

Hello John,

I tried your solution with a similar issue but it still failed - not sure what I do wrong. In principle I like to achieve the very same as Wjtam206.

But I need to replace anything from URL starting with the query parameters, which are indicated by "?" or "&". These could occur on any level of a URL, such as 

www.homepage/?...

www.homepage/subpage1/&...

www.homepage/subpage1/subpage2/?... and so forth

And of course, if there is none of these, the full path should be returned. 

Would you have an idea on how to get there?

Many thanks in advance!

Joerg