Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reference tables from multiple ODBC sources to restrict records on load

I have three tables that i would like to load, however because of their size, i am going to filter the records prior to load; an issue arises because the third table is on a different server; wondering how to reference table 1 in a where exists clause for table 3.

the structures are similar to below, and more detail are posted further down. 

table 1

EmailCustIDCountry
email1@noemail.com123US
email2@noemail.com456US
email3@noemail.com789US
email4@noemail.com333CA
email5@noemail.com888MX

CustIDAge
12323
45645
78960
33354
88819

table 3

CustIDOrderTotal
12355.12
456100.45
78962.18
33377.79
88812.12

i planned to filter only for US

so i wrote the following:

ODBC CONNECT TO [SERVERNAME XXXXXX1] (XUserId isXXXXX, XPassword is XXXX);

SQL SELECT

    EMAIL,

   CUSTID,

  COUNTRY

FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');

--Filter table2 to include only those records that exist in table 1

SQL SELECT

    CUSTID,

   AGE,

FROM "SERVER"."DBNAME"."TABLE2" WHERE CUSTID in (SELECT CUSTID FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');)

Table3 however is on another server; is there a way to reference table1?

I would need to a new connection string. Wondering if i can name the table1 with an alias and reference it like:

ODBC CONNECT TO [SERVERNAME XXXXXX2] (XUserId isXXXXX, XPassword is XXXX);

SQL SELECT

    CUSTID,

   ORDERTOTAL,

FROM "SERVER2"."DBNAME"."TABLE3" WHERE CUSTID in (SELECT CUSTID FROM ALIAS_FOR_TABLE_1)

1 Solution

Accepted Solutions
Sokkorn
Master
Master

Hi James,

You may try this load script

ODBC CONNECT TO [SERVERNAME XXXXXX1] (XUserId isXXXXX, XPassword is XXXX);

[Table1]:

SQL SELECT

    EMAIL,

   CUSTID,

  COUNTRY

FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');

[CustID]:

LOAD Concat(CustID,',') AS CustIDVal Resident [Table1];

Let vCondition = Peek('CustIDVal',0,'CustID');

DROP Table [CustosID];

[Table2]:

SQL SELECT

    CUSTID,

   AGE,

FROM "SERVER"."DBNAME"."TABLE2" WHERE CUSTID in ($(vCondition));

ODBC CONNECT TO [SERVERNAME XXXXXX2] (XUserId isXXXXX, XPassword is XXXX);

SQL SELECT

    CUSTID,

   ORDERTOTAL,

FROM "SERVER2"."DBNAME"."TABLE3" WHERE CUSTID in ($(vCondition));

Hope this help.

Regards,

Sokkorn

View solution in original post

1 Reply
Sokkorn
Master
Master

Hi James,

You may try this load script

ODBC CONNECT TO [SERVERNAME XXXXXX1] (XUserId isXXXXX, XPassword is XXXX);

[Table1]:

SQL SELECT

    EMAIL,

   CUSTID,

  COUNTRY

FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');

[CustID]:

LOAD Concat(CustID,',') AS CustIDVal Resident [Table1];

Let vCondition = Peek('CustIDVal',0,'CustID');

DROP Table [CustosID];

[Table2]:

SQL SELECT

    CUSTID,

   AGE,

FROM "SERVER"."DBNAME"."TABLE2" WHERE CUSTID in ($(vCondition));

ODBC CONNECT TO [SERVERNAME XXXXXX2] (XUserId isXXXXX, XPassword is XXXX);

SQL SELECT

    CUSTID,

   ORDERTOTAL,

FROM "SERVER2"."DBNAME"."TABLE3" WHERE CUSTID in ($(vCondition));

Hope this help.

Regards,

Sokkorn