Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

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

Celambarasan

Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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
Author

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
Author


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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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