Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
whalenem
Contributor II
Contributor II

Filter data out of archive database across two connections

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?

2 Replies
petter
Partner
Partner

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
  12. ODBC CONNECT TO main; 
  13. LEFT JOIN(Order
  14. LOAD 
  15.   "create-date" as OrderCreateDate, 
  16.   ... 
  17.   "ship-loc" as OrderShipLocation
  18. SQL SELECT 
  19.   "create-date"
  20.   ... 
  21.   "ship-loc"
  22.   ... 
  23.   l.location 
  24.   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
Contributor II
Contributor II
Author

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.