Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shilpan
Luminary Alumni
Luminary Alumni

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
shilpan
Luminary Alumni
Luminary Alumni
Author

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);

View solution in original post

6 Replies
Gysbert_Wassenaar

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
shilpan
Luminary Alumni
Luminary Alumni
Author

It is in the same format:

2011-09-06T20:36:38.000Z

Please advise.

Thanks,

Shilpan

rwunderlich

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

YYYY-MM-DD 00:00:00

-Rob

shilpan
Luminary Alumni
Luminary Alumni
Author

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);

rwunderlich

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

mananmehta900
Contributor III
Contributor III

Hello,

It's great that things worked out for you using Timestamp, but if you would still like to use Date function for the where clause, please use like function in the where clause.

 

EX: 

where FileDate1 like Date(WeekStart(Today()))

 

FileDate1 is a date. something like "02/02/2020"

 

Thanks,
Manan Mehta