Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Showing main URL on separate column

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

Tags (2)
1 Solution

Accepted Solutions

Re: Showing main URL on separate column

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


talk is cheap, supply exceeds demand
6 Replies

Re: Showing main URL on separate column

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

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


talk is cheap, supply exceeds demand

Re: Showing main URL on separate column

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.

Not applicable

Re: Showing main URL on separate column

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

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


talk is cheap, supply exceeds demand
Not applicable

Re: Showing main URL on separate column

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;

Community Browser