topic multiple databases with same tables in QlikView

multiple databases with same tables

Mon, 13 Jun 2011 01:43:03 GMT

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...

multiple databases with same tables

nagaiank — Mon, 13 Jun 2011 02:14:32 GMT

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.

Re: multiple databases with same tables

Tue, 14 Jun 2011 01:19:38 GMT

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?

Re: multiple databases with same tables

Tue, 14 Jun 2011 02:01:50 GMT

hey axon, try this...

Table1:

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

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

Re: multiple databases with same tables

nagaiank — Tue, 14 Jun 2011 02:09:10 GMT

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.

Re: multiple databases with same tables

Tue, 14 Jun 2011 02:24:26 GMT

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

could you paste ur codes here?

Re: multiple databases with same tables

Tue, 14 Jun 2011 02:26:10 GMT

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

Re: multiple databases with same tables

Tue, 14 Jun 2011 02:47:10 GMT

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 ?

multiple databases with same tables

Tue, 14 Jun 2011 03:04:28 GMT

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

Re: multiple databases with same tables

nagaiank — Tue, 14 Jun 2011 03:06:11 GMT

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.

multiple databases with same tables

Tue, 14 Jun 2011 03:44:48 GMT

i can't do this?

connect site1

load table1

load table2

...more tables...

connect site2

concatenate table1

concatenate table2

...more tables...

and so on...

multiple databases with same tables

Anonymous — Tue, 14 Jun 2011 03:52:53 GMT

Sure you can

multiple databases with same tables

Tue, 14 Jun 2011 03:59:21 GMT

but im getting suffixes adding on to the concatenated tables when i connect to a database once to retrieve all the tables and then the next database instead of repeatedly connect to the databases for the next table...

table.fieldA

table.fieldB

table-1.fieldA

table-2.fieldB

for kris' example, he consantly connects to the database to simulate multiple databases

multiple databases with same tables

Anonymous — Tue, 14 Jun 2011 04:01:02 GMT

So what does your script look like then? Could you paste the doc here?

Re: multiple databases with same tables

nagaiank — Wed, 15 Jun 2011 15:28:57 GMT

You do not need to connect to the database for each table.

Connect to Site1 database

// load tables

table1:

...

table2:

Then connect to site2 database

load tables

Hope this helps.

multiple databases with same tables

Thu, 16 Jun 2011 01:34:07 GMT

thanks to kris, johannes and nick i have managed to get what i wanted

it was actually my qualify/unqualify statements that were causing the suffixes to be appended instead of concatenating to the tables...

multiple databases with same tables

Thu, 16 Jun 2011 02:00:31 GMT

ahhhhh now i got another problem

i wasn't joining all tables yet when i removed the qualify/unqualify statements

i was only joining tables with 2 similar columns

but once i started joining the other tables which were similar but site-specific i will be stuck at the loading script window...

multiple databases with same tables

TK_ — Mon, 20 Jun 2011 12:47:55 GMT

why are you using join and not concatenate ?

multiple databases with same tables

nagaiank — Mon, 20 Jun 2011 14:02:53 GMT

For any table, if there are site-specific fields in addition to common fields, you need to define all the fields of a table before you start loading from the sites. For example,

Common fields of all sites: Com1, Com2, Com3

Site1-specific fields: Site1a, Site1b

Site2-specific fields; Site2a, Site2b

Define all fileds of the Table1 using the script

Table1:

LOAD * Inline [

Com1, Com2, Com3, Site1a, Sit1b, Site2a, Site2b

];

Connect to Site0 and load data

Concatenate (Table1) LOAD * From Table1

Connect to Site1 and load data

Concatenate (Table1) LOAD * From Table1

...

Hope this helps.