Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Help selecting dates after a certain date

error.jpg

Sorry if this is simple... I'm reading my data from a pervasive sql database, everything works perfectly, but when I
add load "DDate" it always crashed with OLE DB multiple step operation error, I'm assuming it's because the DDate has too many

transactions (it's years worth of invoices).   How can I make DDate only load transactions after a certain date, E.g.  01/01/2013   ?
Thank you in advance

1 Solution

Accepted Solutions
Not applicable

Re: Help selecting dates after a certain date

http://www.pervasive.com/Portals/55/documents/PSQLv9/readme_v970.htm

I found the problem, it does not lay with Qlikview. The problem was PSQL V9.  The above hotfix for PSQL
address the following issue

58407

Invalid date/time/timestamp error from UDF called in a stored procedure

So this is sorted out. Thank you so much to all of you, I have no doubt your answers were perfect.

31 Replies
MVP
MVP

Re: Help selecting dates after a certain date

Put a where clause in your SQL statement like:

SQL Select .....

From ....... Where DDate>= '01/01/2013' ;

Note: this '01/01/2013'  part of your code has to have exactly the same format of date as in your SQL database.

Not applicable

Re: Help selecting dates after a certain date

try this

Load CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode  where DDate>'01/01/2013';

SQL Select CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode

From HistoryLines;

**************

make sure date DDate in the format of your this date 01/01/2013,

if not in right format--

then use-

WHERE Date(DDate,'DD/MM/YYYY')>'01/01/2013'

Not applicable

Re: Help selecting dates after a certain date

OR TRY THIS

Load CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode  ;

SQL Select CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode

From HistoryLines  where DDate>'01/01/2013';

Not applicable

Re: Help selecting dates after a certain date

Wow, you guys are amazing, thank you for the quick response.

I tried both, the top one still gives me the exact same problem (with where ddate after load)
this is a picture of the error and the order of DDate so you can see it http://i.imgur.com/j8vHLOx.jpg

with where ddate after select,  it fails and says multiple errors occured.  

I appreciate your help so much

MVP
MVP

Re: Help selecting dates after a certain date

The where clause has to be there in SQL Select. I can't see your error message, can you post it here directly using the camera icon<insert image>?

Not applicable

Re: Help selecting dates after a certain date

Untitleder.jpg

There's the error with it in LOAD,  the bottom left is the layout of DDate.
Below is the error with it in SELECTin select.jpg

MVP
MVP

Re: Help selecting dates after a certain date

Please check the DDate date format in the SQL database (as I mentioned earlier). Say it is in timestamp format like  - 'dd/mm/yyyy hh:mm:ss', then you have to put the where condition date like:

Where DDate > '01/08/2013 00:00:00'

Not applicable

Re: Help selecting dates after a certain date

also remember ; at the end

Not applicable

Re: Help selecting dates after a certain date

There is ; at the end

tres, I am confused by your post... I have put a picture in my last post and at the bottom right you can see the DDate column of the table with its format..    It says

"ErrorSource: Pervasive.SQL V9 OLE DB Provider, ErrorMsg: One or more errors occurred during processing of command.

SQL Select CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode

From HistoryLines Where DDate > '01/08/2013 00:00:00' "

Community Browser