Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
its_anandrjs

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

View solution in original post

9 Replies
its_anandrjs

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

tresesco
MVP
MVP

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
Specialist II
Specialist II

Changes

Last Time i made some mistake now i corrected that one

where  Len(Trim(mail))>0;

tresesco
MVP
MVP

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

its_anandrjs

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

nizamsha
Specialist II
Specialist II

srry for that

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

Not applicable
Author

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

its_anandrjs

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
Author

Interesting. Good to know. Thanks!