Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Why does this WHERE clause result in Script Error?

Hello,

I'm trying to read users from the Active Directory. Since there are so many records in the AD, I'm trying to add a WHERE clause to limit the number returned.

I can run the following script in debug mode, set number of recs to bring back to 500 and it works fine.

2014 0519_09.04.20.AM_0001.jpg

However, if I add the following WHERE statement I get a Script Error

   WHERE length > 0

2014 0519_08.57.46.AM_0000.jpg

Can anyone tell me why that WHERE statement would cause a Script error?

Thanks in advance for any help.

1 Solution

Accepted Solutions

Re: Why does this WHERE clause result in Script Error?

You have to write

Len(mail) > 0 in place of the length because it is not identified because length field is identified by Len(mail) if you read this in another table then it is identified. Also place this line in before SQL like

Load Distinct

Fields

..

Len(mail) as length

where Len(mail) > 0;

SQL select

Fields

From location

9 Replies

Re: Why does this WHERE clause result in Script Error?

You have to write

Len(mail) > 0 in place of the length because it is not identified because length field is identified by Len(mail) if you read this in another table then it is identified. Also place this line in before SQL like

Load Distinct

Fields

..

Len(mail) as length

where Len(mail) > 0;

SQL select

Fields

From location

MVP
MVP

Re: Why does this WHERE clause result in Script Error?

Possibly, you have to write it like:

Where Len(mail)>0;    

Because the field Length is not yet created in your source data. Else use the same where condition in the qv load part.

nizamsha
Valued Contributor

Re: Why does this WHERE clause result in Script Error?

Changes

Last Time i made some mistake now i corrected that one

where  Len(Trim(mail))>0;

MVP
MVP

Re: Why does this WHERE clause result in Script Error?

Nizam, if trim() has to be used, it would be Len(Trim(mail)) instead.

Re: Why does this WHERE clause result in Script Error?

Yes it will be  Trim  and then Len function but not required here.

nizamsha
Valued Contributor

Re: Why does this WHERE clause result in Script Error?

srry for that

thanks u very much  for reminding me,  i miss typed that  one

Not applicable

Re: Why does this WHERE clause result in Script Error?

Anand,

Thanks for your response. Your suggestions worked. The part about not using the new field name in the WHERE clause makes sense. However, the part about putting the WHERE clause before the SQL key word doesn't.

I mean, it works but I don't understand why. Every example I've seen always has the WHERE clause after the FROM clause inside the SQL keyword. Even the help system says to

Even the help system indicates that the WHERE comes after FROM. Strange.

2014 0519_09.31.15.AM_0002.jpg

Re: Why does this WHERE clause result in Script Error?

See like from any SAP tables or may be SQL tables when we read the tables that has two parts one for SQL and another one for the QLIkview like a preceding load. So if we put the Where clause before the SQL will read by the qlikview table it depends on from which source we are reading is some thing like first we read the table and then filter the table with our requirements.

Not applicable

Re: Why does this WHERE clause result in Script Error?

Interesting. Good to know. Thanks!

Community Browser