Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Can you post the complete query?Also Which database are you using?
Celambarasan
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
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
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
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
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
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
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
Hi , Many thanks for your help - something tells me I have a lot to learn !Regards,Simon