Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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:
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.
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.