topic Re: Multiple Database in a simple script in QlikView

Multiple Database in a simple script

bsbernabe — Wed, 20 Nov 2013 06:45:50 GMT

Hi there,

I have tried to use multiple database in ODBC connect to qlikview, but i don't know the exact logic to link this two database

Here my script:

ODBC CONNECT TO RAPS_DEV (XUserId is RURLDZFMQbcEE, XPassword is LOXOEZFMQbcCDZMGbF);

SQL SELECT FIRST 50 A.EVALHDRID, A.ACCTMASTERID,A.KMREADING,A.PLATENO,A.MPORNO,

E.EVALHDRID AS "SUMRY_HDRID", E.TOTALCOSTAMT, E.SELLINGPRICE, E.CLASSIFICATION,

M.ASSETMAKE, M.ASSETMODEL, M.ENGINENO, M.CHASSIS, M.COLOR,B.BRANCHCODE AS "MC_BRANCH"

FROM EVALHDR A

JOIN

(SELECT EVALHDRID, TOTALCOSTAMT, SELLINGPRICE, CLASSIFICATION

FROM EVALPRICESUMRY)E ON E.EVALHDRID = A.EVALHDRID

JOIN (SELECT BRANCHCODE,MCMASTERID FROM ACCTMASTER)B ON B.MCMASTERID = A.ACCTMASTERID

JOIN (SELECT MCMASTERID, ASSETMAKE, ASSETMODEL, ENGINENO, CHASSIS, COLOR FROM MCMASTER)M ON M.MCMASTERID = A.MCMASTERID;

ODBC CONNECT TO BIMS (XUserId is UbXGLRFNNbcCD, XPassword is AJfCPRFNNbcCDZMGPG);

SQL SELECT F.BRANCHCODE, F.BRANCHSNAME

FROM BIMS.BRANCH F ON F.BRANCHCODE = B.BRANCHCODE;

Please need advise. Thank you

Re: Multiple Database in a simple script

Wed, 20 Nov 2013 07:16:23 GMT

Hi Bernabe,

Hope the following code will help..

ODBC CONNECT TO RAPS_DEV (XUserId is RURLDZFMQbcEE, XPassword is LOXOEZFMQbcCDZMGbF);

Table1: //Load the Sql result

Load EVALHDRID,

ACCTMASTERID,

KMREADING,

PLATENO,

MPORNO,

SUMRY_HDRID,

TOTALCOSTAMT,

SELLINGPRICE,

CLASSIFICATION,

ASSETMAKE,

ASSETMODEL,

ENGINENO,

CHASSIS,

COLOR,

MC_BRANCH

SQL SELECT FIRST 50 A.EVALHDRID, A.ACCTMASTERID,A.KMREADING,A.PLATENO,A.MPORNO,

E.EVALHDRID AS "SUMRY_HDRID", E.TOTALCOSTAMT, E.SELLINGPRICE, E.CLASSIFICATION,

M.ASSETMAKE, M.ASSETMODEL, M.ENGINENO, M.CHASSIS, M.COLOR,B.BRANCHCODE AS "MC_BRANCH"

FROM EVALHDR A

JOIN

(SELECT EVALHDRID, TOTALCOSTAMT, SELLINGPRICE, CLASSIFICATION

FROM EVALPRICESUMRY)E ON E.EVALHDRID = A.EVALHDRID

JOIN (SELECT BRANCHCODE,MCMASTERID FROM ACCTMASTER)B ON B.MCMASTERID = A.ACCTMASTERID

JOIN (SELECT MCMASTERID, ASSETMAKE, ASSETMODEL, ENGINENO, CHASSIS, COLOR FROM MCMASTER)M ON M.MCMASTERID = A.MCMASTERID;

ODBC CONNECT TO BIMS (XUserId is UbXGLRFNNbcCD, XPassword is AJfCPRFNNbcCDZMGPG);

Table2: //Load the Sql result

Load BRANCHCODE,

BRANCHSNAME

SQL SELECT F.BRANCHCODE, F.BRANCHSNAME

FROM BIMS.BRANCH F ON F.BRANCHCODE = B.BRANCHCODE;

//Now Join the Both Tables

Main_Table

Load EVALHDRID,

ACCTMASTERID,

KMREADING,

PLATENO,

MPORNO,

SUMRY_HDRID,

TOTALCOSTAMT,

SELLINGPRICE,

CLASSIFICATION,

ASSETMAKE,

ASSETMODEL,

ENGINENO,

CHASSIS,

COLOR,

MC_BRANCH as BRANCHCODE

Resident Table1;

Drop Table Table1;

inner join (Table1)

Load BRANCHCODE,

BRANCHSNAME

Resident Table2;

Drop Table Table2;

Re: Multiple Database in a simple script

bsbernabe — Wed, 20 Nov 2013 09:07:21 GMT

Hi there,

i appreciate you fast respond, my concern is about the two database how to link to each other?

first database RAPS_DEV

table: EVALHDR

field ACCTMASTERID and

ACCTMASTER

field MCMASTERID, BRANCHCODE

second database BIMS

table: BRANCH

field BRANCHCODE, BRANCHSNAME

my problem is how can i connect the first database field to the second database field?

Highly appreciated your respond

Thanks.

Re: Multiple Database in a simple script

Wed, 20 Nov 2013 09:27:25 GMT

Hi,

When you load data from sources (databases, files, hard coded data), this data is inside tables.

The tables are linked if you have fields in common: it means with the same name.

In other words, if you have two tables with a Product field, they will be joined. Automatically.

If you want to load the second source into the same table as the 1st one, you must:

- either have the same fields names

- use the CONCATENATE keyword

- use a JOIN before the LOAD statement

Fabrice