Connections with more than one databases in the same document

[#1]

I am creating a qlikview document which presents inventory, sales, and other aspects of our 42 stores. The data for the stores are stored in servers which uses postgresql. we have two servers storing  20 of the 42 stores in one sever and the other 22 are stored on the other server. structurally the database of all the stores are identical (same schemas and tables). I have created two DSNs for the two servers for an ODBC connection. I have been able to load the tables from one of the servers and create few tables and charts. It works fine. However, now I want to connect all the stores(from both the servers) to that document so that I can browse through the inventory or sales of any store from the same document.

Is it possible?

How do I arange my script to be able to do that? when I created two connections it only considers latest connection to pull the tables from the database.

Please, give some ideas or direct me to relavent documents or forum.

[Ferran_Garcia_Pagans]

Hi,

I've an Oracle db with 2 differnt schemas 'sh' and 'oe'. I use this code to connect to the 2 different schemas and load data.

ODBC CONNECT TO [orcl-rh;DBQ=ORCL ] (XUserId is XIKIAUJOeA, XPassword is NZRWAUJOZC);

LOAD "EMPLOYEE_ID",

    "FIRST_NAME",

    "LAST_NAME",

    EMAIL,

    "PHONE_NUMBER",

    "HIRE_DATE",

    "JOB_ID",

    SALARY,

    "COMMISSION_PCT",

    "MANAGER_ID",

    "DEPARTMENT_ID";

SQL SELECT *

FROM HR.EMPLOYEES;

ODBC CONNECT TO [orcl-sh;DBQ=ORCL ] (XUserId is bBUAXZBNWG, XPassword is ZVbbVZBNfG);

LOAD "CHANNEL_ID",

    "CHANNEL_DESC",

    "CHANNEL_CLASS",

    "CHANNEL_CLASS_ID",

    "CHANNEL_TOTAL",

    "CHANNEL_TOTAL_ID";

SQL SELECT *

FROM SH.CHANNELS;

I think the same idea would work for your databases.

regards, Ferran

[rbecher]

Hi,

maybe a better approach is to put all the load statements into on sub and call it twice:

sub loading

LOAD ...

LOAD ...

end sub

connect first_db ...;

call loadsub;

connect second_db ...;

call loadsub;

HTH

- Ralf

[#4]

Thank you, Ferran and Ralf.

I am using the following code to connect to the first server:

ODBC CONNECT TO SERVER1;

Stores:

LOAD var1, var2, var3, var4;

SQL

SELECT * FROM s19.table1 Where notetype <> 0  UNION

SELECT * FROM s20.table1 Where notetype <> 0  UNION

SELECT * FROM s21.table1 Where notetype <> 0  UNION

SELECT * FROM s25.table1 Where notetype <> 0  UNION

SELECT * FROM s26.table1 Where notetype <> 0  UNION

SELECT * FROM s27.table1 Where notetype <> 0  UNION

SELECT * FROM s28.table1 Where notetype <> 0  UNION

SELECT * FROM s29.table1 Where notetype <> 0  UNION

SELECT * FROM s30.table1 Where notetype <> 0  UNION

SELECT * FROM s31.table1 Where notetype <> 0  UNION

SELECT * FROM s32.table1 Where notetype <> 0  UNION

SELECT * FROM s33.table1 Where notetype <> 0  

;

This loads the data for store numbers greater than 19 into table "Stores". I have made few interactive charts using this table. Now, Our first 18 stores are on different server. My question is, how can I load the data for those 18 stores also into Stores table so that my already created charts and tables work for those stores too.

Is it possible?

Like, Feran and Ralf suggested, I can create two saparate tables for the data from the two servers but then can i link those tables to the same charts? In other words, can charts and tables take data from different tables depending upon the selection of the store?

I hope I have not made it more complecated. please ask me any clarification if you need.

[rbecher · Accepted solution]

You can load multiple times into one table. In your case I would do a for .. each loop with a vStore variable:

ODBC CONNECT TO SERVER1;

for each vStore in 's01', 's02', ...etc.

Stores:

SQL SELECT var1, var2, var3, var4 FROM $(vStore).table1 Where notetype <> 0; // No UNION needed!

next vStore;

ODBC CONNECT TO SERVER2;

for each vStore in 's19', 's20', ...etc.

Stores:

SQL SELECT var1, var2, var3, var4 FROM $(vStore).table1 Where notetype <> 0; // No UNION needed!

next vStore;

------------

If the store numbers are ongoing you could also use a FOR .. NEXT loop with a counter variable:

for vStore = 1 to 19

..

next;

------------

- Ralf