Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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;
Let me try this and I will get back to you.
Thanks
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;