Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I've the same table stored into several db and I need to gather all data into one table. Is it possible to create a loop, changing the odbc connection ?
How can I cope this scenario ?
Help please.
Thanks in advance
Hi,
I see several questions so let me break them down and respond.
1. Is it possible to create a loop
Yes, you can use VBSCRIPT loops in Qlikview
// Load all excelsheets with a loop
// DB is the variable table
// DBLIST is the table containing all the databases.
for each DB in DBLIST
SQL SELECT FROM $(DB);
Next ExcelFile;
2. Is it possible to change the odbc connection
Yes, see the example below.
3. How can I cope with this scenario.
The best way to handle this is to load your data from different sources and directly save them in a
qvd. You can then use the qvd's as you see fit.
Assuming the tables are in the same format and position:
Example:
CONNECT TO odbc1;
odbc1:
SQL SELECT * FROM A;
STORE * FROM odbc1 INTO odbc1.qvd;
DROP TABLE odbc1;
CONNECT TO odbc2;
odbc2:
SQL SELECT * FROM A;
STORE * FROM odbc1 INTO odbc2.qvd;
DROP TABLE odbc2;
A:
LOAD * FROM odbc1.qvd (qvd);
concatenate
LOAD * FROM odbc2.qvd (qvd);
Let me know if you need more help to construct this
Dion Verbeke
Certified QV11 Designer, Developer, Administrator
Hi,
I've the same problem.
Do you have any further ? I've more than 20 db and the example above is not the best
Hi,
Can you describe your issue with a sample document or an scanned image.
I will do my best to give you a concrete solution.
Kind Regards,
Dion
Hi bibopipo,
you can do this in the load script with a for..each loop. Lets say you have 3 source databases with the same table:
FOR EACH vConnectStr IN 'CONN1', 'CONN2', 'CONN3'
ODBC CONNECT TO [$(vConnectStr)];
qvtable:
SELECT col1, col2, ..., colx FROM table;
NEXT
- Ralf
Dear all,
I solved (I hope) in this way:
LET Number_of_records = NoOfRows('MytableList');
FOR i=0 to $(Number_of_records)-1
let DBName=peek('NOME_DB',$(i),'MytableList');
let Firsttime = 0;
ODBC CONNECT TO $(DBName) (XUserId is MyUser, MyPassword is VaaZSRZMULZGXUJd);
If $(Firsttime) = 0 THEN
Mytable0:
LOAD
......
......
FROM
E:\myfolder\my_data\t130tl$(DBName).qvd
(qvd);
Mytable2:
LOAD
............
............
FROM
E:\myfolder\my_data\t135tl$(DBName).qvd
(qvd);
set Firsttime = 1;
ELSE
Mytable1:
concatenate(mytable0)
LOAD
..............
..............
FROM
E:\myfolder\my_data\t130tl$(DBName).qvd
(qvd);
Mytable3:
concatenate(Mytable2)
LOAD
.............
............
FROM
E:\myfolder\my_data\t135tl$(DBName).qvd
(qvd);
ENDIF
next
Now I've two tables , Mytable_0 and Mytable2 with all records.
Thanks to everyone has spent time to help me.
See my last message, I think is what you are looking for