Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Using resident table in an ODBC connection?

I am trying to load data from 2 different databases and I would like to use the results from the first database table to limint the records selected from the second database. Can I do some type of resident load? Thanks.

ODBC CONNECT TO DATASOURCE1;

TABLE1:

LOAD

VAR1,

VAR2,

VAR3 ;

SQL SELECT

C.VAR1,

C.VAR2,

C.VAR3

FROM SOURCE.TABLE C

WHERE C.VAR1 > '00000000' ;

ODBC CONNECT TO DATASOURCE2;

TABLE2:

LOAD

VAR4,

VAR5,

VAR6 ;

SQL SELECT

A.VAR4, B.VAR5, D.VAR6

FROM RESIDENT TABLE1 C

JOIN SOURCE.TABLEB B ON SUBSTR(B.SEQUENCE,1,10)=C.SEQUENCE

JOIN SOURCE.TABLEA A ON A.SEQUENCE=SEQUENCE

JOIN SOURCE.TABLEA D ON D.SEQUENCE_NUM = B.SEQUENCE_NUM

WHERE C.VAR1 > '00000000' AND D.VAR6 IS NOT NULL ;

4 Replies
mov
Esteemed Contributor III

Using resident table in an ODBC connection?

It's not exactly clear what you're trying to do... A few notes that may help.
First, when you load data from two databases, before you connect to the 2nd database, there shuold be a statement
DISCONNECT.
Second, when you load from a resident table, the syntax is
tablename:
LOAD
...
...
RESIDENT PreviouslyLoadedTable
WHERE...;

Not applicable

Using resident table in an ODBC connection?

Thanks for the reply.

Basically I am trying to use the results from the 1st table to limit the number of rows I select from the 2nd table. I was hoping to not have to pull in all the records from the 2nd table then do a join on the 2 tables. The below does work but I have to first pull in all the records from the 2nd data source.

combo:
LOAD
....
...
resident File1;
Inner Join
LOAD
....
...
resident File2 ;

mov
Esteemed Contributor III

Using resident table in an ODBC connection?

Actually it is possible not to load all records from the 2nd table:
table1:
LOAD
...
RESIDENT File1;
...
LOAD
...
WHERE exists() (or "not exists()")
SQL SELECT
...
FROM DB.Table2;

Search how to use exists() function here on forum - there are more than a few.

Not applicable

Re: Using resident table in an ODBC connection?

Hi,

Were you able to get a solution. I am also trying to achieve the same in Qlik Sense. Can you please provide some insight.

Thanks!