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: 
Not applicable

multiple databases with same tables

i have multiple databases for different operating sites but all of them have the same tables and i do not have a column that says where the record is from since all of them reside in site-specific databases so there was no need in the first place...

how do i join them together but still be able to differentiate which sites the records are from?

thanks in advance...

18 Replies
nagaiank
Specialist III
Specialist III

You may add a field to each table identifying the source site as follows:

// Connect to site1 database

OLEDB connect to connection-string-of-site1

// load individual tables

Table1:

Load 'Site1' as Source, filed1, field2, ... from ....

Table2:

Load 'Site1' as Source, field1,field2, ... from ...

...

// Connect to site2 database

OLEDB connect to connection-string-of-site2

Concatenate (Table1) Load 'Site2' as Source, field1, field2, ... from ...

Concatenate (Table2) Load 'Site2' as Source, ...

...

// Repeat the above procedure for all sites

Depending on the architecture, you may have to tweak the script.

Hope this helps.

Not applicable
Author

hi kris thanks for your reply,

i followed your steps but i seem to get additional tables in the end,

when i do a select fields or new sheet object, the fields listed there are like this:

tablename.fieldA

tablename.fieldB

tablename-1.fieldA

tablename-1.fieldB

why is this so, isn't it supposed to be just tablename instead of the additional tablename-1? did i do something wrong?

also, i did the loading of tables from different sites in different tabs of the script editor. that can't be the problem right?

Not applicable
Author

hey axon, try this...

Table1:

Load 'Site1' as Source, filed1, field2, ... from ....

CONCATENATE Load 'Site1' as Source, field1,field2, ... from ...

nagaiank
Specialist III
Specialist III

I tried to load from four different tables of an Access database by disconnecting the database connection after each load (in order to simulate multiple sites) and I do not get multiple tablenames with suffixes generated by the load.

I have attached my qvw file.

Not applicable
Author

sorry kris i'm unable to open your qvw, i'm using the free personal edition...

could you paste ur codes here?

Not applicable
Author

but this will only work if i load table1 from siteA then table1 from siteB, one table at a time right?

Not applicable
Author

yes, one table at a time by script, i think there's no way u can do parellel loading by script, unless, u use dummy qvw and then load into QVD, but again, because of CONCATENATE, it wont make much sense loading it together , i think. does the script work for u ?

Not applicable
Author

i don't need parallel loading, just one time connections to each site's database, get all the site's database's tables, then connect to the 2nd site's database, get all the 2nd site's database's tables and concatenate their rows together...

if i have to do it table by table, i have to repeatedly connect to each site's database

nagaiank
Specialist III
Specialist III

Here is the script that I used.

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

Table1:

LOAD *, 'Site1' as Source;

LOAD Field1, Field2, Field3, Field4;

SQL SELECT * FROM Site1;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

Table2:

LOAD *, 'Site1' as Source;

LOAD FieldA, FieldB, FieldC, FieldD;

SQL SELECT * FROM Site1;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

Concatenate (Table1) LOAD *, 'Site2' as Source;

LOAD Field1, Field2, Field3, Field4;

SQL SELECT * FROM Site2;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

Concatenate (Table2) LOAD *, 'Site2' as Source;

LOAD FieldA, FieldB, FieldC, FieldD;

SQL SELECT * FROM Site2;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

Concatenate (Table1) LOAD *, 'Site3' as Source;

LOAD Field1, Field2, Field3, Field4;

SQL SELECT * FROM Site3;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

Concatenate (Table2) LOAD *, 'Site3' as Source;

LOAD FieldA, FieldB, FieldC, FieldD;

SQL SELECT * FROM Site3;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

Concatenate (Table1) LOAD *, 'Site4' as Source;

LOAD Field1, Field2, Field3, Field4;

SQL SELECT * FROM Site4;

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

Concatenate (Table2) LOAD *, 'Site4' as Source;

LOAD FieldA, FieldB, FieldC, FieldD;

SQL SELECT * FROM Site4;

DISCONNECT;

As you can see, I have loaded Table1 and Table2, followed by Table1 and Table2 from the second site, etc.

Hope this helps.