topic looping a script in QlikView

looping a script

ali_hijazi — Wed, 26 Sep 2012 11:06:01 GMT

Hello I got to fetch data from several servers that contain the same database schema (i.e. same table names,etc...)

I want to change the connection string on each loop and save each table in a distinct qvd

what I want to parameterize is the following:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=entUAE;Initial Catalog=PS_DEV;User ID=RO;Password=RO;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=HANI-SAGHEER;Use Encryption for Data=False;Tag with column collation when possible=False];
F4311:LOAD *;SQL
Select dbo.fn_ConvertJulianToDate(PDTRDJ) as PDDATE, * from testdta.F4311 where PDDCTO in ('OM','OC','OL');

store F4311 into $(vQvdPath)F4311_entUAE.qvd(qvd);DROP Table F4311;

the info in yellow highlight will change according to the loop.

Re: looping a script

whiteline — Wed, 26 Sep 2012 11:08:39 GMT

Hi.

You can use 'for' statement to loop it.

Also you could create an inline table with serverNames to pick one for each iteration with fieldvalue() or peek() functions.

Re: looping a script

ali_hijazi — Wed, 26 Sep 2012 11:13:11 GMT

using peek or fieldvalue works for the load script as follows:

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

LET sheetName = 'vDolphin.dbo.' & peek('name',i,'tables');

LOAD *;

sql select * from $(sheetName);

but how can I make the connection string in the ODBC Connect or OLEDB Connect dynamic?

Re: looping a script

ali_hijazi — Wed, 26 Sep 2012 11:28:19 GMT

as for the connection string you can store the connection strings in txt files and inside the for loop you can read the desired txt file using the following command:

$(Include=$(i).txt);

Re: looping a script

whiteline — Wed, 26 Sep 2012 13:05:33 GMT

You can also use the same approach as with $(vQvdPath).