topic Re: Need Help in Script Where condition in QlikView

Need Help in Script Where condition

Mon, 28 Dec 2015 11:35:19 GMT

Hi,

I would like to load only list of numbers from the database, so I am manually writing every day like as

Load

AAA,

BBB,

CCC;

Sql Select

AAA,

BBB,

CCC

Where CCC IN('51',52');

My question is I would like to give the CCC variable value numbers in excel file or in a text file.

Thanks in advance to advise ...

Re: Need Help in Script Where condition

Anonymous — Mon, 28 Dec 2015 11:40:08 GMT

You mean to say, you want to define those values of CCC in Excel and then use CCC in where condition, is it?

What issue you are facing with manual porcess?

Re: Need Help in Script Where condition

PrashantSangle — Mon, 28 Dec 2015 11:41:39 GMT

Hi,

Use exist()

Regards

Re: Need Help in Script Where condition

tresB — Mon, 28 Dec 2015 11:42:53 GMT

Try it like:

List:

Load

CCC

From <Excel>;

Tab:

Load

AAA, BBB, CCC

From <> Where exists (CCC);

Drop table List;

Re: Need Help in Script Where condition

PrashantSangle — Mon, 28 Dec 2015 11:43:16 GMT

Hi,

For example

ExcelFile:

Load CCC from excel;

Table:

Load AAA,

BBB,

CCC

from tableName where exist(CCC,CCC)

Regards

Re: Need Help in Script Where condition

Mon, 28 Dec 2015 11:50:13 GMT

Thanks for your reply,

When I copy from the CCC value in excel, I am getting the following error.

Token œ was not valid. Valid tokens: ( + - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.

Re: Need Help in Script Where condition

Mon, 28 Dec 2015 11:51:10 GMT

Let me try your example....

Thanks and Regards...

Re: Need Help in Script Where condition

Mon, 28 Dec 2015 12:51:12 GMT

Hi,

If I use exits, I am getting the error of SQL0104 - Token EXIST was not valid. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT.

Exist('96732','96733','96734','96735','96736','96737','96738')

Re: Need Help in Script Where condition

tresB — Mon, 28 Dec 2015 12:57:02 GMT

Exists() is a qlikview function; can't be executed in SQL. You should rather try directly the SQL statement (with IN ). Qlikview sends the sql statement to SQL engine in the dbms and it's get executed there. Hence, you can use the SQL statement as it is directly here in qv.

SQL Select AAA, BBB, CCC

From <> Where CCC IN('51',52') ;

Re: Need Help in Script Where condition

Mon, 28 Dec 2015 13:05:07 GMT

Hi Tresesco,

Thanks for your reply, I tried before In function only in the sql statement, the

When I copy from the CCC value in excel file and paste in the qv script, I am getting the following error, and when I manually key the numbers in the qv script its working fine, I don't know where I making mistake.

Error message:

Token œ was not valid. Valid tokens: ( + - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.

Thanks to proceed how do i proceed

Re: Need Help in Script Where condition

MarcoWedel — Sat, 02 Jan 2016 22:06:38 GMT

Hi,

maybe you could combine Max' solution with a preceding load to the SQL select like this:

tabTemp:
LOAD CCC
From YourExcelFile;

table1:
LOAD *
Where Exists(CCC);
SQL Select
        AAA,
        BBB,
        CCC
From YourDBTable;

DROP Table tabTemp;

or maybe with a join instead of Exists():

table1:
LOAD CCC
From YourExcelFile;

Left Join (table1)
SQL Select
        AAA,
        BBB,
        CCC
From YourDBTable;

Please not that these solutions load all CCC values from your DB, so the script run time might be longer compared to reducing the CCC values already in the select statement.

hope this helps

regards

Marco