topic Re: Help selecting dates after a certain date in QlikView

Help selecting dates after a certain date

Wed, 07 Aug 2013 07:48:17 GMT

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

Re: Help selecting dates after a certain date

tresB — Wed, 07 Aug 2013 07:55:06 GMT

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.

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 07:56:58 GMT

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'

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 07:59:10 GMT

OR TRY THIS

Load CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode ;

SQL Select CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode

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

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 08:09:20 GMT

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

Re: Help selecting dates after a certain date

tresB — Wed, 07 Aug 2013 08:25:14 GMT

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>?

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 08:30:34 GMT

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

Re: Help selecting dates after a certain date

tresB — Wed, 07 Aug 2013 08:38:22 GMT

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'

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 08:43:15 GMT

also remember ; at the end

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 08:54:17 GMT

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' "

Re: Help selecting dates after a certain date

tresB — Wed, 07 Aug 2013 09:11:45 GMT

Well, let's try it step by step to find the root cause.

See if the below statement fetches data successfully:

SQl Select

CustomerCode,

ItemCode,

DDate

From HistoryLines Where CustomerCode=<give once code>;

Does it work? If yes, then connection is ok; statment has no problem, we can try for step2 then.

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 09:13:31 GMT

you should not use timestamp.

Re: Help selecting dates after a certain date

Wed, 07 Aug 2013 11:08:02 GMT

Sorry about the late reply, Im here now. It threw the same error, Tres qv

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

SQL Select

CustomerCode,

ItemCode,

DDate

From HistoryLines Where CustomerCode=<give once code>"

Re: Help selecting dates after a certain date

Thu, 08 Aug 2013 07:04:48 GMT

Any help ?

Re: Help selecting dates after a certain date

Thu, 08 Aug 2013 07:06:35 GMT

can u share application?

Re: Help selecting dates after a certain date

tresB — Thu, 08 Aug 2013 07:11:41 GMT

'<give once code>', i meant a CustomerCode which is there in the database. Like CustomerCode='101' .

Re: Help selecting dates after a certain date

MayilVahanan — Thu, 08 Aug 2013 07:16:28 GMT

Hi darrenrsa,

Try like this

Load CustomerCode, ItemCode,Qty, Date(DDate,'DD/MM/YYYY') as DDate, SalesmanCode where DDate >= Date('01/08/2013','DD/MM/YYYY');

SQL Select CustomerCode, ItemCode, DDate, Qty, SalesmanCode From HistoryLines;

Hope that helps

Re: Help selecting dates after a certain date

Thu, 08 Aug 2013 08:38:23 GMT

Thanks Mayil, It didn't wok, it gave the error

ErrorSource: Pervasive.SQL V9 OLE DB Provider, ErrorMsg: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

SQL Select

CustomerCode,

ItemCode,

DDate,

Qty,

SalesmanCode

From HistoryLines

It's only if a put DDate in, if I remove it, everything else loads perfectly and is viewable etc

Thomas, I can share the application, but the database is too big to share and I fear you cant do anything with the application without the database ?

Re: Help selecting dates after a certain date

Thu, 08 Aug 2013 08:39:59 GMT

Just need a sample! But try anyway

Re: Help selecting dates after a certain date

Thu, 08 Aug 2013 09:20:08 GMT

Where shall I share it ? Sorry for the begginer question