topic Re: Use data from resident load in SQL query in QlikView

Use data from resident load in SQL query

andrewmo — Tue, 05 Dec 2017 18:20:05 GMT

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.

Re: Use data from resident load in SQL query

its_anandrjs — Tue, 05 Dec 2017 18:31:22 GMT

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

Re: Use data from resident load in SQL query

its_anandrjs — Tue, 05 Dec 2017 18:36:45 GMT

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;

Re: Use data from resident load in SQL query

andrewmo — Tue, 05 Dec 2017 18:38:39 GMT

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.

Re: Use data from resident load in SQL query

el_aprendiz111 — Tue, 05 Dec 2017 19:22:30 GMT

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) );

Re: Use data from resident load in SQL query

andrewmo — Tue, 05 Dec 2017 20:25:38 GMT

That appears to do the trick. Thanks much!

Re: Use data from resident load in SQL query

its_anandrjs — Tue, 05 Dec 2017 20:35:23 GMT

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.

Re: Use data from resident load in SQL query

andrewmo — Tue, 05 Dec 2017 20:39:27 GMT

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.

Re: Use data from resident load in SQL query

Anonymous — Thu, 13 Dec 2018 18:55:42 GMT

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?