Filter data out of archive database across two connections

[whalenem]

So, we have a two main databases (one for US and one for Canada) and an archive database. I need information from the main database and country specific data from the archive database. I can load all the data with...

ODBC CONNECT TO archive;

Order:

LOAD

  "create-date" as OrderCreateDate,

  ...

   "ship-loc" as OrderShipLocation

;

SQL SELECT

  o."create-date",

...

  o."ship-loc"

  FROM PUB.order o

  ;

But what I need to do is something like...

ODBC CONNECT TO main;

ODBC CONNECT TO archive;

Order:

LOAD

  "create-date" as OrderCreateDate,

  ...

  "ship-loc" as OrderShipLocation

;

SQL SELECT

  o."create-date",

  ...

  o."ship-loc",

  ...

  l.location

  FROM main.PUB.location l

    LEFT JOIN archive.PUB.order o

      ON o."ship-loc" = l.location

  ;

The next hitch in this process is that while the connect statements have different names the archive DB's name in the ODBC connection and the main DB's name are the same. Yeah. Is there a way I can alias the DB in the connect statement? How can I pull a location listing from the main DB (so only US locations are involved) and the order listing from the archive DB?

[petter]

You can only have a single connection open at any given part of the load script. However the connection doesn't have to limit you to accessing only a single database.

The most efficient solution would be to do the join on the back-end database if possible. Then you would only retrieve the results into QlikView.

If joining on the back-end is not possible for you for some reason you could first draw in one of the tables first then connect to the other database and join in the second table but then use the JOIN operator in QlikView load script between the tables:

1. ODBC CONNECT TO archive; 
2. Order: 
3. LOAD 
4.   "create-date" as OrderCreateDate, 
5.   ... 
6.    "ship-loc" as OrderShipLocation; 
7. SQL SELECT 
8.   "create-date", 
9. ... 
10.   "ship-loc" 
11.   FROM PUB.order; 
13. ODBC CONNECT TO main; 
14. LEFT JOIN(Order) 
15. LOAD 
16.   "create-date" as OrderCreateDate, 
17.   ... 
18.   "ship-loc" as OrderShipLocation; 
19. SQL SELECT 
20.   "create-date", 
21.   ... 
22.   "ship-loc", 
23.   ... 
24.   l.location 
25.   FROM main.PUB.location l;

Make sure that all the columns/fields that are used as join criteria is named the same. The reason for this is that QlikView's own JOIN doesn't allow to join on fields except by a so-called natural join. The fields that have matching names in both tables will be used on an equality join basis.

If the Order table from archive is much larger than the Order table from main I would rather pull the smaller table in first (which goes into memory) and then do a RIGHT join instead - it should be less memory intensive.

1. ODBC CONNECT TO main; 
2. Order: 
3. LOAD 
4.   "create-date" as OrderCreateDate, 
5.   ... 
6.    "ship-loc" as OrderShipLocation
7.    location as l.location; 
8. SQL SELECT 
9.   "create-date", 
10. ... 
11.   "ship-loc" ,
12.   location
13.   FROM main.PUB.location; 
14.  
15. ODBC CONNECT TO archive; 
16. RIGHT JOIN(Order) 
17. LOAD 
18.   "create-date" as OrderCreateDate, 
19.   ... 
20.   "ship-loc" as OrderShipLocation;
21. SQL SELECT 
22.   "create-date", 
23.   ... 
24.   "ship-loc"
25.   FROM PUB.order; 

[whalenem]

Thank you!

If the Order table from archive is much larger than the Order table from main I would rather pull the smaller table in first (which goes into memory) and then do a RIGHT join instead - it should be less memory intensive.

I'd seen some done like this, but you answered the question I didn't even know I needed to ask.