Use database names from Excel sheet in a For Loop for Connection String.

[#1]

Hi,

I am a newbie to Qlikview and coding in general. I need some help figuring out how to do a for loop.

What I have is an Excel spreadsheet with two columns ProjectName and DatabaseName.

I brought in the excel file like this..

ANProjects:

LOAD

     ProjectName,

     DatabaseName

FROM

(ooxml, embedded labels, table is Sheet1);

Which brings the columns in to Qlikview, Now I want to create a For loop that will use the database names row by row in the connection string.

Example connection string, blue area is where I need to connect the Database.

OLEDB CONNECT32 TO [Provider=ABCDEDB.1;Persist Security Info=True;User ID=BI_QLIKVIEW;Initial Catalog=$(databaseName);Data Source=ABCD1P;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=00000;Use Encryption for Data=False;Tag with column collation when possible=False] (XPASsword is BLANK);

So I do not know how to load the database row by row which might involve a variable.

Each database will give the project more fields which are associated with different project names.

Instead of having to do a new connection string each time, and defining the fields I need I want to automate the process by pulling in all fields from each database, in a For Loop.

Example excel spread sheet:

ProjectName	DatabaseName
Project1	project1_db
Project2	project2_db
Project3	project3_db

Please let me know if I am not making myself clear.

Thank you

Alam

[ecolomer]

Hi

Here you are an example:

// list files "classment.html" en hard disc

DIRECTORY C:\Mis lugares Web\F1\www.statsf1.com\es;

Set Root='C:\Mis lugares Web\F1\www.statsf1.com\es';

FOR any=1950 to 2013;

   FOR Each Dir in DirList ('$(Root)\$(any)'&'\*.')

       FOR Each File in FileList ('$(Dir)'&'\class*.html')

  Res:

       LOAD

              '$(any)' as GP_Año,

              Mid('$(Dir)',47) as Pais_f,

              Pos as GP_Posición,

              N° as GP_Num,

              Piloto as GP_Piloto,

              Chasis as GP_Equipo,

              Motor as GP_Motor,

              Vuelta as GP_Vueltas,

             F7 as GP_Tiempo,

             Pts as GP_Puntos

           FROM [$(File)]

                  (html, utf8, embedded labels, table is @1)

                where N°>0;

         NEXT File

     NEXT Dir

NEXT any

[maxgro · Accepted solution]

do you mean this?

t:

load * inline [

ProjectName, DatabaseName

Project1, project1_db

Project2, project2_db

Project3, project3_db

];

// fill variable with field values row by row

For i = 0 To NoOfRows('t')-1

  let vDatabaseName=peek('DatabaseName', $(i));

  let vProjectName=peek('ProjectName', $(i));

  trace $(vDatabaseName)  $(vProjectName);

  // .......... use connection string ???

next;

[#4]

Let me try this and I will get back to you.

Thanks

[#5]

Thank you Massimo

This does work it runs through each database and connects to each one. Now I need to figure out how to take the data in for each database by Concatenating the table called (Defects) I created.

Let me know if you can help further, thanks.

I have this so far.

// fill variable with field values row by row

For i = 0 To NoOfRows('ANProjects')-1

  let vDatabaseName=peek('DatabaseName', $(i));

  let vProjectName=peek('ProjectName', $(i));

  trace $(vDatabaseName)  $(vProjectName);

  // .......... use connection string ???   // Added the new variable from the for loop.

  OLEDB CONNECT32 TO [Provider=ABCDEDB.1;Persist Security Info=True;User ID=BI_QLIKVIEW;Initial Catalog=$(vDatabaseName);Data Source=ABCD1P;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=00000;Use Encryption for Data=False;Tag with column collation when possible=False] (XPASsword is BLANK); 

Let vDefectsTableName = 'Defects'; // Define Table Name

$(vDefectsTableName):

// Load everything in to table variable

LOAD *;

SQL SELECT *

FROM $(vDefectsTableName);

// Get information from Table in database

SELECT *

FROM tablename; /*Defect*/  //Table in each database is the same name.

next;