topic Validate a url in QlikView

Validate a url

RSvebeck — Mon, 31 Oct 2011 13:00:50 GMT

I have a Qlikview application that contains a list of http:\\ ... "URLS". Each URL is expected to link to an image or a PDF document, but sometimes the links are "invalid", the picture or document can have been removed, the web server can be offline, or the link is not spelled correctly etc.

I need to add a quality control in my Qlikview document and let the Qlikview users know if the links are ok and give statistics on how many links are unavailable etc.

Is it possible to let Qlikview do this check somehow for me, perhaps with the help of a macro - triggered by a macro button or do you think this has to be done before the load script with a external application (if so, can anyone recommend a software for this....?)

The links are supplied to me by a ordinary text file.

Any hints and tips that guide me in the right direction is appreciated!

Best Regards, Robert Svebeck

Robert Svebeck

Validate a url

swuehl — Mon, 31 Oct 2011 13:30:17 GMT

Robert,

you could use VBScript macro code like this:

http://www.robvanderwoude.com/vbstech_internet_website.php

I am not sure what's best, checking within your application or beforehand, IMHO it's depending on what your want to do with this information.

For example ,you could use above code snippet in a macro called from your script and then stop execution and alert the administrator, maybe.

Anyway, hope this helps,

Stefan

Validate a url

flipside — Mon, 31 Oct 2011 14:58:46 GMT

Hi Robert,

Just using the Function part of the script in Stefan's link, this can be called in the load script as follows ...

WebSite:
LOAD * INLINE [
    website
www.cncncbvnvb.com
    www.bbc.co.uk
    www.sky.com
];

Tmp=NoOfRows('WebSite');

For i = 1 to $(Tmp)

url=FieldValue('website',$(i))

vb_in=PingSite(url)

If vb_in <> 0 then
JOIN (WebSite)
LOAD website, 'Valid' as validURL resident WebSite where website='$(url)' ;
ELSE
JOIN (WebSite)
LOAD website, 'Broken' as validURL resident WebSite where website='$(url)' ;
ENDIF;

Next i;

// Loop creates some nulls which need clearing out
tmp: NOCONCATENATE LOAD DISTINCT * resident WebSite where Match(validURL,'Valid','Broken') > 0;

DROP TABLE WebSite;
RENAME TABLE tmp to WebSite;

Cheers

flipside

Validate a url

RSvebeck — Wed, 02 Nov 2011 10:27:35 GMT

Thanks for your support gyus.

The solution you both provided works for verifying that the domain is valid or not, but it doesn't work for my image urls.

The links are as I wrote links to either documents or images, often the link is a url like this:

http://www.example.com/cgi-bin/lank.dll/lev?customer=7771&item=534358

That is - it's a database request-url "post", and the result is a "redirect" to a new url with a "real" imagelink.

Best Regards

Robert Svebeck

Validate a url

swuehl — Wed, 02 Nov 2011 12:58:05 GMT

Hi Robert,

so you get a lot of 'broken' results though the link is valid?

The referenced code snippet only checks for html return code = 200 (OK), if a redirect is returned, you get html return codes in the 3xx range. Maybe it's just enough then to change the check in the macro code from equal 200 to < 400. Give it a try.

And/ or you may try to debug into the code and have a look what's returned in your real environment.

Hope this helps,

Stefan

Validate a url

RSvebeck — Thu, 03 Nov 2011 08:28:42 GMT

Hi again,

I tested my document from home and here it works great, so the problem is that I have to handle the proxy servers at work....

So now I have to figure out how to setup WinHTTP with proxy support.

I have seen the function SetProxy for the WinHTTP object,

But I don't get it to work, I think our proxy also requires a login, but I am not sure yet.

Anyway, the solution works when I'm not inside the office... Thanks again for all help!

Btw:

I rewrote the load script like this:

LOAD @1 as Link, PingSite(@1) as status
FROM welinks_source.txt (txt, codepage is 1252, no labels, delimiter is '\t', msq);

Skipping the For Next Join part completely, and instead having the function "PingSite" handle more of what to give back to the load script, makes the script more simple to read.

I was not aware that it was possible to write functions to be used in the load script. This functionality is superb, gives me a lot of ideas how to solve other challenges!

Regards
Robert Svebeck

Validate a url

Wed, 04 Apr 2012 16:44:34 GMT

HI Robert,

I am also new to Qlikview and have similar situation. Where can I find PingSite script that will work in Qlikview script? Or maybe I need references on how to include the PingSite VBScript from Qlikview document.

Thanks

Stanley

Re: Validate a url

RSvebeck — Thu, 05 Apr 2012 09:58:00 GMT

Hi Stanley,

I will happily share my final solution to you.See attached document.