topic Re: Validating URL's in QlikView

Validating URL's

Sun, 23 Mar 2014 16:56:12 GMT

Hi, I have an excel file which contains URL's in one field and I would like to validate those URL's whether it is valid or Invalid. Currently I am doing it by manual but I have more than 10K which I need to validate without manual. I am looking forward to avoid manual work, Is it possible to do with Qlikview? Or is there any function or VB macro in to achieve this. Thanks, URL Web Site - Found www.clippingpath.co.uk Valid www.ourmanagement.net Invalid www.digitalphotoservices.co.uk Valid www.glproductins.dk Invalid

Re: Validating URL's

swuehl — Sun, 23 Mar 2014 17:24:10 GMT

You can try it like I suggested here:

Validate a url

Re: Validating URL's

rbecher — Sun, 23 Mar 2014 18:03:27 GMT

Hi Madu,

this seems to work:

URL_List:
LOAD if(left(URL,7)<>'http://','http://'&URL,URL) as URL;
LOAD * INLINE [
    URL
    www.clippingpath.co.uk
    www.ourmanagement.net
    www.digitalphotoservices.co.uk 
    www.glproductins.dk
];

FOR i=0 to NoOfRows('URL_List')-1

    Let vURL = peek('URL', $(i), 'URL_List');
    
    URL_Valid:
    FIRST 1 LOAD 
    '$(vURL)' as URL, 
    @1 as first_line, 
    if(@1='<html>', 0, 1) as is_valid
    FROM $(vURL)
    (txt, codepage is 1252, no labels, delimiter is '\t', msq);

NEXT

You could try to read the first line from the HTML page of the URL. If the domain doesn't exists QlikView gives back "<html>". Of course an existing website's HTML page could also start with a plain "<html>" tag but this is very unusual nowadays with CMS systems and xhtml sites.

- Ralf

Re: Validating URL's

Anonymous — Fri, 18 Aug 2017 03:59:00 GMT

When I tried to use the above script in Qlik sense, there is error message saying: This statement only works with lib:// paths in this script mode.

Any suggestion?

Thanks!

Re: Validating URL's

rbecher — Fri, 18 Aug 2017 13:58:15 GMT

Web sources doesn't work this way in Qlik Sense. You can use the Qlik REST connector instead.

Re: Validating URL's

Anonymous — Fri, 18 Aug 2017 14:14:46 GMT

Thanks so much for your help!

I am not going to load data from web, so I did not use Qlik REST connector.

I have a list of URL, and I am going to check which URL is valid and which is not valid, and count the number in Qlik sense sheet. Could you please give any more suggestion?

Re: Validating URL's

rbecher — Fri, 18 Aug 2017 16:05:43 GMT

If you do not load data from the web how else you could check an URL is valid?

Re: Validating URL's

Anonymous — Fri, 18 Aug 2017 17:59:12 GMT

Thanks so much for your help!

Take the URL in your script as example:

www.clippingpath.co.uk

www.ourmanagement.net

www.digitalphotoservices.co.uk

www.glproductins.dk

I would like to check which URL in the above is down, and then create a pie chart in Qlik sense to show how many is down and how many is OK.

If using Qlik Sense REST connector, we can only check one URL each time and it is also manual.

Is there a way we can check with script as you show in the above?

When I tried to use the above script in Qlik sense, there is error message saying: This statement only works with lib:// paths in this script mode.

Any further suggestion?

Re: Validating URL's

rbecher — Fri, 18 Aug 2017 19:00:35 GMT

You can create a REST connection use WITH CONNECTION to give the URL dynamically:

URL_List:  
LOAD if(left(URL,7)<>'http://','http://'&URL,URL) as URL;  
LOAD * INLINE [  
URL  
http://www.clippingpath.co.uk  
http://www.ourmanagement.net  
http://www.digitalphotoservices.co.uk   
http://www.glproductins.dk  
];  

LIB CONNECT TO 'CheckURL';

FOR i=0 to NoOfRows('URL_List')-1  

      Let vURL = peek('URL', $(i), 'URL_List');  

    SET ErrorMode=0;

    URL_valid:
    LOAD '$(vURL)' as URL, *;
    SQL SELECT 
      "Vary",
      "Keep-Alive",
      "Connection",
      "Accept-Ranges",
      "Content-Length",
      "Content-Type",
      "Date",
      "ETag",
      "Last-Modified",
      "Server",
      "__KEY__response_header"
    FROM CSV "_response_header" PK "__KEY__response_header"
    WITH CONNECTION(Url "$(vURL)");

    SET ErrorMode=1;

NEXT