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

Why does this WHERE clause result in Script Error?

Mon, 19 May 2014 13:08:52 GMT

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.

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

WHERE length > 0

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

Thanks in advance for any help.

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

its_anandrjs — Mon, 19 May 2014 13:14:16 GMT

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

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

tresB — Mon, 19 May 2014 13:16:22 GMT

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.

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

nizamsha — Mon, 19 May 2014 13:19:58 GMT

Changes

Last Time i made some mistake now i corrected that one

where Len(Trim(mail))>0;

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

tresB — Mon, 19 May 2014 13:23:00 GMT

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

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

its_anandrjs — Mon, 19 May 2014 13:26:04 GMT

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

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

nizamsha — Mon, 19 May 2014 13:32:09 GMT

srry for that

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

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

Mon, 19 May 2014 13:35:24 GMT

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.

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

its_anandrjs — Mon, 19 May 2014 13:47:08 GMT

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.

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

Mon, 19 May 2014 14:02:14 GMT

Interesting. Good to know. Thanks!