Showing main URL on separate column

[#1]

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

[rubenmarin]

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

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

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

[Gysbert_Wassenaar · Accepted solution]

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

[rubenmarin]

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.

[#5]

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

[Gysbert_Wassenaar]

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

[#7]

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;