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