Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where clause filter data with previous table


Hi,

I have a excel file with 10 Product_Sub_group_ code

and Teradata table with Millions of rows.

I have done script like below but give me an error

Tbl1:

LOAD Product_Sub_Group_Code
FROM

(
ooxml, embedded labels, table is import);

Tbl2:
SQL SELECT *
FROM BUYER_LOOKUP
WHERE exists (Tbl1.Product_Sub_Group_Code)

drop table Tbl1;

error:---

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the word 'Tbl1'.
Tbl2:
SQL SELECT *
FROM BUYER_LOOKUP
WHERE exists (Tbl1.Product_Sub_Group_Code)

Note: I do not want Full Teradata table to be load. Can we use where clause to filter data using Tbl1?

5 Replies
alexandros17
Partner - Champion III
Partner - Champion III

the clause Exists can be used with qlik syntax not sql. So you should have to load all the table in teradata then reload it with the exists condition but in this case you have to load all data.

Another solution is (but I do not know is syntax is correct) is:

SELECT *
FROM BUYER_LOOKUP
WHERE Tbl1.Product_Sub_Group_Code in ('Code1','Code2','Code3', ....,'Code10')

where codes 1..10 are codes you have in your Excel

Let me know

maxgro
MVP
MVP

don't think in this way because not exists is a QlikView syntax and it only works in the Load section (but in the load section you're not in Teradata)

Maybe you can use a variable to  identify the Product_Sub_Group_Code to filter

and then use the variable in the second statement (SQL), pseudocode

load

     .....

set variablewithproductsubgroupcode = ......;

sql select

     ...

WHERE fieldinteradata in ('$()variablewithproductsubgroupcode')

Not applicable
Author

Hi Saccone,

I will use IN clause but It was just example of 10 rows but accualy there are Hundred of Code.

we can do for all Codes.

Any other solution?

Regards & Thanks

Vinay

alexandros17
Partner - Champion III
Partner - Champion III

the only thing is load all data from teradata, (during night?) and store them in a qvd file. Then load all data in your document from the qvd and finally reload them with exists, the advantage is that you load all data in a different moment from the loading of the document

maxgro
MVP
MVP

try something like this (load inline is a replacement of your load from excel)


TB1:

load * inline [

Product_Sub_Group_Code

aaaa

bbbbb

cccccccc

ddddd

aaa bbb

e

f

e

f

g

];

B:

load

  '(' & chr(39) & concat(distinct Product_Sub_Group_Code, chr(39) & ',' & chr(39)) & chr(39) & ')' as SqlFilter

Resident

  TB1;

LET v = Peek('SqlFilter');

Tbl2:

SQL SELECT *

FROM BUYER_LOOKUP

WHERE Product_Sub_Group_Code in $(v);




the query you send to db is

SELECT *

FROM BUYER_LOOKUP

WHERE Product_Sub_Group_Code in ('aaa bbb','aaaa','bbbbb','cccccccc','ddddd','e','f','g')