topic same table in several databases in QlikView

same table in several databases

Anonymous — Sat, 15 Dec 2012 17:54:17 GMT

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

Re: same table in several databases

Sat, 15 Dec 2012 21:02:45 GMT

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;

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

Re: same table in several databases

Wed, 19 Dec 2012 18:55:55 GMT

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

Re: same table in several databases

Wed, 19 Dec 2012 20:32:59 GMT

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

Re: same table in several databases

rbecher — Wed, 19 Dec 2012 21:09:12 GMT

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;

- Ralf

Re: same table in several databases

Anonymous — Wed, 19 Dec 2012 21:37:29 GMT

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

Now I've two tables , Mytable_0 and Mytable2 with all records.

Thanks to everyone has spent time to help me.

Re: same table in several databases

Anonymous — Wed, 19 Dec 2012 21:38:11 GMT

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