Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a question trimming a URL string in qlik.
For example,
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!
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
Hi, Wesley. Maybe something like this
SubField(Replace(If(Right(UrlString, 1) = '/', Left(UrlString, Len(UrlString)-1), UrlString), 'https://www.', ''), '/#', 1)
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!
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
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?
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.
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!
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
It works now. Thank you!
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/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