Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

same table in several databases

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

6 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

See my last message, I think is what you are looking for