Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bibopipo
Contributor II

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

Re: same table in several databases

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

Re: same table in several databases

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

Re: same table in several databases

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

MVP & Luminary
MVP & Luminary

Re: same table in several databases

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

bibopipo
Contributor II

Re: same table in several databases

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.

bibopipo
Contributor II

Re: same table in several databases

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