topic Re: Where Clause in QlikView

Where Clause

Fri, 21 Dec 2012 18:56:02 GMT

I'm trying to use the Where Clause to only select the last two years but keep getting error message.

Re: Where Clause

MayilVahanan — Fri, 21 Dec 2012 18:59:27 GMT

Qlikview is case sensitive.

SO, Please check year field..

YEAR or Year or year? And also is Year field available in db?

Re: Where Clause

Fri, 21 Dec 2012 19:01:50 GMT

Sorry the dummy question, but are you sure the year field in the where clause is exactly writen as existing in the db you are loading?

Re: Where Clause

Fri, 21 Dec 2012 19:06:18 GMT

the field is "DATESUBMITTOOPEN" and the Format is MM-DD-YYYY HH:MM:SS AM but i want to select just the last two years. is this possible?

Re: Where Clause

MayilVahanan — Fri, 21 Dec 2012 19:09:53 GMT

Try like this

Select * From tableName where Year(DATESUBMITTOOPEN) >= 2010 and Year(DATESUBMITTOOPEN) <= 2012;

Re: Where Clause

Fri, 21 Dec 2012 19:14:56 GMT

Sure it's possible, but you need to transform your data first in order to create a year reference.

you can try the following:

Select *

From YourTable

Where Year(DateSubMitToOpen)>=xxxx and Year(DateSubMitToOpen)<yyyy

The xxxx and yyyy are the year values you are using as the min and max timestamp values you want to load

If you need a more automated load script then some more steps are required

Regards

Re: Where Clause

Fri, 21 Dec 2012 19:21:26 GMT

Re: Where Clause

MayilVahanan — Fri, 21 Dec 2012 19:37:13 GMT

Can you say, did u use any YEAR field or set in any variable in your script?

There is no error in the script, it work fine. Can you provide a script which you are using?

Re: Where Clause

Fri, 21 Dec 2012 20:13:06 GMT

Yes Year(DateSubMitToOpen) as DateSubMitToOpen

Re: Where Clause

MayilVahanan — Fri, 21 Dec 2012 20:39:06 GMT

Its difficult to say without your script.

Try like this

Generally load all the data, like this

Load Year(DateField) as Year, FieldName1,FieldName2;

Select * from tableName where Year(DateField) >= 2010 and Year(DateField) <= 2012;

Re: Where Clause

Fri, 21 Dec 2012 21:52:02 GMT

Can you copy and share the part of the scrip where you load and filter the data?

Did you check if you ended the instruction with a semi-colon?

Generally, the format of a Load / Select script is the following:

//Start of the select instruction

Select *

From YourTable

Where Year(DateSubMitToOpen)>=xxxx and Year(DateSubMitToOpen)<yyyy

;

// End of Select instruction

Another source of the error warning maybe the fact that your field "DateSubMitToOpen" as a timestamp may not be a Date field but a text field instead.

If that's the fact you need to transform this text field in a Date

Can you check that?