Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andrewmo
Creator
Creator

Use data from resident load in SQL query

Hello

Can someone direct me on how to use a resident load as part of an IN statement in an SQL where clause?

I have a primary key that is populated in a load statement that I wan to utilize in the where statement of an sql query.

Here is an example of the script:

IDSTG:

LOAD        ID 

FROM       excelsheet; //contains 500 ID values

LET vID = PEEK('ID','IDSTG');

DEMO:

LOAD          DISTINCT *;

SQL

SELECT     ID, Date, Name

FROM         Tbl1

WHERE      ID IN ('$(vID)')

I want to use vID like it is a temp table that can be referenced in the SQL script.

1 Solution

Accepted Solutions
el_aprendiz111
Specialist
Specialist

Hi Andrew

1 exm:

TMP:
LOAD * Inline
[
ID,FIELD
1,A
2,C
11,V
34,N
25,U
12,P
18,O
]
;

;
[SUM]:
LOAD Concat(CHR(39) & ID & CHR(39),',') AS ID Resident TMP;


LET v_IN_ID = trim(Peek('ID'));


DROP Table TMP, SUM;

TRACE SELECT  ID, Date, Name FROM Tbl1 WHEREID IN (  $(v_IN_ID) );


IN.png

View solution in original post

8 Replies
its_anandrjs

You can use where exists for this rather than IN clause.

its_anandrjs

This ways

IDSTG:

LOAD      

ID as IDloading

FROM     excelsheet; //contains 500 ID values

DEMO:

LOAD     DISTINCT *

WHERE Exists( IDloading,ID )

SQL

SELECT   ID, Date, Name

FROM      Tbl1;

andrewmo
Creator
Creator
Author

Won't this method still pull everything from the SQL query then apply the WHERE Exists afterwards?

Tbl1 is a large table that take a lot of time to query against.

el_aprendiz111
Specialist
Specialist

Hi Andrew

1 exm:

TMP:
LOAD * Inline
[
ID,FIELD
1,A
2,C
11,V
34,N
25,U
12,P
18,O
]
;

;
[SUM]:
LOAD Concat(CHR(39) & ID & CHR(39),',') AS ID Resident TMP;


LET v_IN_ID = trim(Peek('ID'));


DROP Table TMP, SUM;

TRACE SELECT  ID, Date, Name FROM Tbl1 WHEREID IN (  $(v_IN_ID) );


IN.png

andrewmo
Creator
Creator
Author

That appears to do the trick. Thanks much!

its_anandrjs

NO it is also a good technic to load data from selected ids where you can load the data on the SQL tables it self.

andrewmo
Creator
Creator
Author

It appears to be pulling everything then applying the Where exists afterwards as I can see in the Script Execution Process the number of lines fetched when executing the SQL portion is higher than the number of IDs. Also, I do not have access to write to tables in that SQL server thus is why I needed to figure out how to apply the IDs I needed only.

Anonymous
Not applicable

So this solution only seems to work if the list of values in the IN Statement is less than 1000.  If you have more than 1000 values the sql errors out.  Is there anyway around this limit?