Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted

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

Highlighted

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;

Creator
Creator

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.

Highlighted
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

Highlighted
Creator
Creator

That appears to do the trick. Thanks much!

Highlighted

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.

Highlighted
Creator
Creator

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.

Highlighted
Contributor
Contributor

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?