topic Re: Date comparison on load in QlikView

Date comparison on load

Wed, 21 Mar 2012 08:41:25 GMT

Hi everybody , I'm trying to load order data with a due date >= to today without much success. In my coding I already reformat the due date which is in CYYMMDD format to YYMMDD using

date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') as Due,

If I try and use the same code to load only the records I require I get an error on the date#

FROM AS400.AMFLIB.POHISTI where (date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) >= Today() and INVFG='1' and STAIC<'50' ;

Is this the correct way to do this or can the variable created earlier be used to selct data ?

Many thanks in advance,

Simon

Date comparison on load

CELAMBARASAN — Wed, 21 Mar 2012 09:03:00 GMT

Hi,

Can you post the complete query?Also Which database are you using?

Celambarasan

Re: Date comparison on load

Wed, 21 Mar 2012 09:45:54 GMT

Hi - you're not going to like seeing the whole query , it's a real mess as I'm a complete novice ! We are using MAPICS on an iSeries. All the dates are CYYMMDD so need converting for easy reading. I have already converted a Due Date ok , but if I use the same code to select Due Dates from Today , I get an error saying date# not found in the database.Regards,Simon

Date comparison on load

CELAMBARASAN — Wed, 21 Mar 2012 10:00:11 GMT

Hi,

I'm using personal edition i cann't open your file.

But in error itself it looks like your using Date# function in select query but which is a qlikview function.To limit the date based on today you have to look functions provided in that database.What DB vendor is yours?

Celambarasan

Re: Date comparison on load

Wed, 21 Mar 2012 10:05:12 GMT

Hi , MAPICS is licensed by Infor. Can you use a variable which has been created from the date to select on the load , or does it have to be a database field ? Regards,Simon

Re: Date comparison on load

Wed, 21 Mar 2012 11:44:54 GMT

Hi , would it be better practice to load all records and then just include the records I need in the table ? Where/how would I do this , as an expression using the date function use previously ? Regards,Simon

Re: Date comparison on load

jonathandienst — Wed, 21 Mar 2012 12:11:28 GMT

Simon

Not having seen the whole query I can;t be sure, but it looks like you are trying to use the second Date/Date# expression in the SQL SELECT. The contents of the SQL SELECT are executed by the DBMS, not Qlikview, so they need to be in the correct format for MAPICS.

I am not sure what you mean by CYYMMDD format. Is this 7 digits, or is it the same as YYYYMMDD?

What I would try is to create a variable:

Let vTodayMP = Right(Date(Today(), 'YYYYMMDD'), 7) // asuming 7 digits

Let vTodayMP = Date(Today(), 'YYYYMMDD'); // assuming 8 digits

Then you can use a variable expansion in the SQL query:

FROM AS400.AMFLIB.POHISTI where DUEDT >= '$(vTodayMP)' and INVFG='1' and STAIC<'50' ;

Hope that helps

Jonathan

Re: Date comparison on load

Wed, 21 Mar 2012 12:48:06 GMT

Hi Jonathan , the date format we use ( from MAPICS on an iSeries ) is CYYMMDD - where C = 1 for dates 2000 onwards. So todays date is 1120321. So I either need to find a way of stripping off the century and comparing it or prefixing todays date with a 1. Regards,Simon

Re: Date comparison on load

jonathandienst — Wed, 21 Mar 2012 20:17:25 GMT

Simon

You can still use the approach that I outlined, just define the variable in the Let statement in a way that is compatible with the CYYMMDD format. For example, if the application does not need to reference dates outside the current century:

Let vTodayMP = '1' & Date(Today(), 'YYMMDD');

or more generallly, you may need (if I understand the format correctly)

Let vTodayMP = (int(Year(Today())/100) - 19) & Date(Today(), 'YYMMDD');

Now use this variable as described before

Regards

Jonathan

Re: Date comparison on load

Thu, 22 Mar 2012 13:27:30 GMT

Hi , Many thanks for your help - something tells me I have a lot to learn !Regards,Simon