[ASK] Looping Load multiple ODBC, same database structure

[rendiyan]

Dear QlikView Master,

i have a customer that have multiple database,1 branch = 1 database, with total around 30 branches.

And there's no different in database structure. So i can use 1 query in every database.

But the problem is when i have to concatenate 30 database into 1 qvd.

Is there any way to loop the odbc connection and concatenate the table from 30 databases into 1 qvd?

Best Regards,

Many Thank You

[lironbaram · Accepted solution]

hi

in the script you can use a variable to define the odbc connection string

so your script should look something like

for i=1 to 30

set vODBC = company$(i);

odbc conncet to '$(vODBC);

sql select *

From Table;

next

[tamilarasu]

I am not sure what is your source. I just created a sample file which consolidate .xlsb files using ODBC connection. You can modify it s per your requirment. Have a look at the attached file.

[rendiyan]

Thank you Liron,

It worked, i've combined my script with your solutions.

But first i need to create connection string from qlikview one-by-one and store it into excels files with last semicollumn character removed.

And then i create the loop with your logic.

Below is the result :

Script

Tb_Connection: LOAD Connection FROM [Ms Sources - Mdb.xlsx] (ooxml, embedded labels, table is Sheet1); RESULT_Mdb: LOAD * INLINE [     INITIAL_Mdb     1 ]; FOR nrow = 0 TO noOfRows('Tb_Connection') - 1      LET vBD = peek('Connection',$(nrow),'Tb_Connection');   $(vBD);   Concatenate(RESULT_Mdb)   SQL SELECT CompanyName as Shippers,     ShipperID,     '$(vBD)' as Flag_Mdb   FROM Shippers; Next; drop table Tb_Connection;

Many thank you