topic Re: Showing main URL on separate column in QlikView

Showing main URL on separate column

Tue, 19 Apr 2016 12:42:45 GMT

Dear Community, i'm having trouble with something that I wish you could help me out with.

Basically, i have about 1000 links ordered by popularity (times that they were shared by a user). So i'd like to be able to group them by the main url.

Therefore: http://www.graphlib.net/2015/12/07/in-athe-content-grid-v2-infographic/

Should become: http://www.graphlib.net/

That's the only way I can see which sites publish popular content and make more sense out of my links.

Can anybody help me out? All i know is that if it were Excel, i'd have to do something like =Left(url,FIND("/",url,9)) so that it cuts anything before the 9th character that has a / in it.

Thank you very much !!!

Re: Showing main URL on separate column

rubenmarin — Tue, 19 Apr 2016 14:48:13 GMT

Hi Rob, that Excel expression can be translated to QV as:

=Left(url, Index("/",url,9))

=Left(url, Index(url,"/",9))

Re: Showing main URL on separate column

Gysbert_Wassenaar — Tue, 19 Apr 2016 14:49:59 GMT

Try left(url, index(url,'/',3))

Re: Showing main URL on separate column

rubenmarin — Tue, 19 Apr 2016 14:53:36 GMT

Right, I should have searched before how the FIND function Works in Excel. Seems the 3rd parameter is 'start_position' instead of 'occurrence_number'... or more carefully read the requisites, not just the bolded text.

Re: Showing main URL on separate column

Tue, 19 Apr 2016 15:18:40 GMT

Amazing!! It worked!! I was modifying excel sheets in order to get there.

And if i wanted to show only what's in between the /'s ??

On the Excel sheet what I did was:

1) Strip anything after the slash (which you showed me how to do here)

2) Strip any occurence of "http://www." or "https://www."

3) Strip the final slash

So finally, http://www.graphlib.net/ will show up in QV as graphlib.net

Is there any way to get that done without having to do it manually every time i get a new Excel Spreadsheet??

Thanks so much !!

Re: Showing main URL on separate column

Gysbert_Wassenaar — Wed, 20 Apr 2016 05:36:22 GMT

Perhaps Textbetween(url, '.', '/', substringcount(textbetween(url,'//','/') ,'.')-1)

Re: Showing main URL on separate column

Wed, 20 Apr 2016 12:58:04 GMT

Thanks! But that cut out parts of the link

This is how i solved it in the Load Script:

Load

left(url, index(url,'/',3)) as TestURL2,

From XXX

ShortURL:

Load

title,

lower(trim(subfield(subfield(subfield(subfield(subfield(TestURL2,'http://www.'),'https://www.'),'https://'),'http://'),'/'))) as ShortURL

resident XX;