Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Luminary
Luminary

Date in the Where Clause

Hello,

I'm new to Qlikview. I've been trying to add date range in the where clause, and it is not working. Please advise.

 

CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;T21;XUserId=BESWWZBNJDbAXQZNOZacCYFMULZYGANOVTbIHZVOTDdcSRdNNTLGGYJOOBcEXSBOSbbIGRD;XPassword=TQaULZFMNLYIWQFGQZMKTZET;";

Let vToday = Date(Today(), 'YYYY-MM-DD');
Let vMonthStart = Date(MonthStart(Today(), 'YYYY-MM-DD'));
Let vMStart = '2012-09-01';
//LOAD *
//FROM Case
//Where ClosedDate > '$(vMonthStart)' AND ClosedDate <= '$(vToday)';

LOAD *;
SQL SELECT *
FROM Case
Where ClosedDate > '$(vMStart)' AND ClosedDate <= '$(vToday)';

This is the runtime error message I get: 

Value for the filter criterion for the field 'ClsoedDate' must be of type Datetime and should not be enclosed in the quotes.

I tried without quotes, and it is not working either.

Thanks,
Shilpan

1 Solution

Accepted Solutions
Luminary
Luminary

Re: Date in the Where Clause

Rob,

Now, it's giving me this error:

unexpected token: '2012-10-05T00:00:00Z'

SQL SELECT *

FROM Case

Where ClosedDate > '' AND ClosedDate <= '2012-10-05 00:00:00'

This is the revised code:

Let vToday = Timestamp(Today(), 'YYYY-MM-DD 00:00:00');
Let vMStart = Timestamp(MonthStart(Today(), 'YYYY-MM-DD 00:00:00'));
LOAD *;
SQL SELECT *
FROM Case
Where ClosedDate > $(vMStart) AND ClosedDate <= $(vToday);

5 Replies

Re: Date in the Where Clause

Make sure your vMStart and vToday variables are in the same date format as ClosedDate is in your database.


talk is cheap, supply exceeds demand
Luminary
Luminary

Re: Date in the Where Clause

It is in the same format:

2011-09-06T20:36:38.000Z

Please advise.

Thanks,

Shilpan

Re: Date in the Where Clause

SFDC is expecting a Datetime, you need to format using Qlikview Timestamp. Should be:

YYYY-MM-DD 00:00:00

-Rob

Luminary
Luminary

Re: Date in the Where Clause

Rob,

Now, it's giving me this error:

unexpected token: '2012-10-05T00:00:00Z'

SQL SELECT *

FROM Case

Where ClosedDate > '' AND ClosedDate <= '2012-10-05 00:00:00'

This is the revised code:

Let vToday = Timestamp(Today(), 'YYYY-MM-DD 00:00:00');
Let vMStart = Timestamp(MonthStart(Today(), 'YYYY-MM-DD 00:00:00'));
LOAD *;
SQL SELECT *
FROM Case
Where ClosedDate > $(vMStart) AND ClosedDate <= $(vToday);

Re: Date in the Where Clause

You don't appear to be getting a good value for vMStart. I don't see anything wrong with the example you posted, but verify that in your actual code the spelling and case is exactly the same in your Let and the reference in the SQL.

-Rob

Community Browser