topic [ASK] Looping Load multiple ODBC, same database structure in QlikView

[ASK] Looping Load multiple ODBC, same database structure

rendiyan — Thu, 06 Oct 2016 03:34:53 GMT

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

Re: [ASK] Looping Load multiple ODBC, same database structure

lironbaram — Thu, 06 Oct 2016 05:59:31 GMT

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;

Re: [ASK] Looping Load multiple ODBC, same database structure

tamilarasu — Thu, 06 Oct 2016 06:07:07 GMT

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.

Re: [ASK] Looping Load multiple ODBC, same database structure

rendiyan — Thu, 06 Oct 2016 06:59:34 GMT

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;

Script

Tb_Connection:

LOAD Connection

FROM

[Ms Sources - Mdb.xlsx]

(ooxml, embedded labels, table is Sheet1);

RESULT_Mdb:

LOAD * INLINE [

INITIAL_Mdb

];

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