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