topic Re: Where clause filter data with previous table in QlikView

Where clause filter data with previous table

Tue, 27 Jan 2015 13:01:13 GMT

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?

Re: Where clause filter data with previous table

alexandros17 — Tue, 27 Jan 2015 13:05:49 GMT

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

Re: Where clause filter data with previous table

maxgro — Tue, 27 Jan 2015 13:15:20 GMT

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

Re: Where clause filter data with previous table

Tue, 27 Jan 2015 13:53:16 GMT

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

Re: Where clause filter data with previous table

alexandros17 — Tue, 27 Jan 2015 14:05:55 GMT

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

Re: Where clause filter data with previous table

maxgro — Tue, 27 Jan 2015 19:01:06 GMT

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