topic Read internet file with a loop in QlikView

Read internet file with a loop

Wed, 11 Nov 2009 16:02:53 GMT

Hi,

I'm a new QV user. Maybe my question is trivial.

I want to read several files on the web with different name but with the same structure (web page)

example:

http://www.parisavelo.net/detail-station-11001-b.htm

I know how to read this page.

To help me , I have an excel file with the name of all the files i want to read.

How can i make script to make a loop ?

Thanks for your help

Jean-Jacques

Read internet file with a loop

Anonymous — Wed, 11 Nov 2009 16:12:30 GMT

Hi Jean-Jacques,

Basically what you can do is load all the values from Excel with the filenames into a field. Then you tell QlikView to count the values in the field. After this you loop as many times as there are values, pulling a new value from a new position every time and using that path to load from the specific html file.

This is written without testing in QlikView so there might be some syntax issues:

let vCounter = fieldvaluecount('ExcelFieldWithNames'); //Counting the values from the excel table

for i=1 to $(vCounter) //initiating the loop - as many times as there are values in excel

let vExcelFieldValue = FieldValue('ExcelFieldWithNames',$(i)); //pull the actual URL from position i in the excel file

Load
xxx,
yyy
from $(vExcelFieldValue); //Loading the table from the current URL

next //Moving to the next step where i increases by 1 until it finally hits the number of field values in the excel file

Read internet file with a loop

Wed, 11 Nov 2009 16:52:09 GMT

Thanks Johannes for your help, it works fine (almost).

Just another point. What if the vExcelFieldValue doesn't exist (maybe the value is not synchronize with the URL). Is there a test to prevent the access ?

thanks again

Jean-Jacques

Read internet file with a loop

Anonymous — Wed, 11 Nov 2009 17:29:21 GMT

I hope I understand this correctly. You want some kind of error checking mechanism in place?

What you can do is:

let vCounter = fieldvaluecount('ExcelFieldWithNames'); //Counting the values from the excel table

for i=1 to $(vCounter) //initiating the loop - as many times as there are values in excel

let vExcelFieldValue = FieldValue('ExcelFieldWithNames',$(i)); //pull the actual URL from position i in the excel file

set ErrorMode = 0; //This tells QlikView not to halt on script errors like if the URL doesn't have the same table structure

Load
xxx,
yyy
from $(vExcelFieldValue); //Loading the table from the current URL

if ScriptError=11 then
//In here you can choose what to do if it's a ScriptError 11 for example, Field not found.
end if

next //Moving to the next step where i increases by 1 until it finally hits the number of field values in the excel file

-----------
ScriptError codes:

1 No error
2 General Error
3 Syntax Error
4 General ODBC Error
5 General OLE DB Error
6 General XML Error
7 General HTML Error
8 File Not Found
9 Database Not Found
10 Table Not Found
11 Field Not Found
12 File Has Wrong Format

Read internet file with a loop

Wed, 11 Nov 2009 17:32:36 GMT

Fine !

It' exactly what i want.

thanks

Jean-Jacques