topic Re: Trim URL string in QlikView

Trim URL string

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Trim URL string

andrey_krylov — Wed, 05 Sep 2018 18:42:45 GMT

Hi, Wesley. Maybe something like this

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

Re: Trim URL string

Anonymous — Wed, 05 Sep 2018 18:57:57 GMT

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!

Re: Trim URL string

johnca — Wed, 05 Sep 2018 20:57:02 GMT

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

Re: Trim URL string

Anonymous — Wed, 05 Sep 2018 21:21:28 GMT

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?

Re: Trim URL string

johnca — Wed, 05 Sep 2018 21:25:19 GMT

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.

Re: Trim URL string

Anonymous — Wed, 05 Sep 2018 21:34:29 GMT

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!

Re: Trim URL string

johnca — Wed, 05 Sep 2018 21:51:38 GMT

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

Re: Trim URL string

Anonymous — Wed, 05 Sep 2018 22:36:06 GMT

It works now. Thank you!

Re: Trim URL string

jblank — Fri, 04 Nov 2022 08:10:59 GMT

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

Re: Trim URL string

johnca — Sat, 05 Nov 2022 18:21:19 GMT

Hi Joerg,

It's not clear to me what you need based on the truncated URL's you shared. Can you share the full URL's and expected outputs the same way the originator of this thread did?

Regards,

JohnInSD

(aka johncaqc)

Re: Trim URL string

jblank — Mon, 07 Nov 2022 09:01:07 GMT

Hello Again,

I believe I got there myself:

=IF(SubStringCount(UrlString,'?')>0,LEFT(SubField(UrlString,'?',1),Len(UrlString)),IF(SubStringCount([GA_ZI_Sessions.DataFromQueryURI_dim_pagePath],'&')>0,LEFT(SubField(UrlString,'&',1),Len(UrlString)),UrlString))

Best Regards

Joerg