Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Validating URL's

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

8 Replies
MVP
MVP

Re: Validating URL's

You can try it like I suggested here:

Validate a url

MVP & Luminary
MVP & Luminary

Re: Validating URL's

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

datalakeyu
Valued Contributor

Re: Validating URL's

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!

MVP & Luminary
MVP & Luminary

Re: Validating URL's

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

datalakeyu
Valued Contributor

Re: Validating URL's

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?

MVP & Luminary
MVP & Luminary

Re: Validating URL's

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

Highlighted
datalakeyu
Valued Contributor

Re: Validating URL's

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?

MVP & Luminary
MVP & Luminary

Re: Validating URL's

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