Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date comparison on load

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
1 Solution

Accepted Solutions
MVP
MVP

Re: Date comparison on load

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies

Date comparison on load

Hi,

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

Celambarasan

Not applicable

Re: Date comparison on load

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

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

Not applicable

Re: Date comparison on load

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

Not applicable

Re: Date comparison on load


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

MVP
MVP

Re: Date comparison on load

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Date comparison on load

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

MVP
MVP

Re: Date comparison on load

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Date comparison on load

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