Multiple Database in a simple script

[bsbernabe]

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

[#2]

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;

[bsbernabe]

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.

[#4]

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