9 Replies Latest reply: Mar 22, 2012 9:27 AM by simonperry RSS

Date comparison on load

simonperry
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 Adhimulam

    Hi,

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

     

    Celambarasan

    • Re: Date comparison on load
      simonperry

      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 Adhimulam

        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
          simonperry

          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
          simonperry


          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
            jonathan dienst

            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
              simonperry

              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
                jonathan dienst

                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