6 Replies Latest reply: Jan 17, 2012 4:03 AM by Gabriel Vázquez RSS

    Date problem

      Hi all.

       

      I have a problem with the range of dates, I hope someone can help me.

       

      I have two tables, notes and invoices, the key field is a MAKEDATE (year, month, day). Two weeks ago not invoiced sales are not updated, return values ??are changed only when invoiced sales and not invoiced as null. In 2011 it works !!

       

      Anyone know what could happen? attachment qvw

        • Date problem
          Henric Cronström

          I am not sure I understand your question completely, but I think that a part of the problem lies in the fact that you have entries in your Details table for which there are no corresponding entry in the Facturas table.

          Then you create your master calendar based on the dates in the Facturas table. As a consequence you have dollars in the Details tablethat are associated with dates that do not exist in the other two tables, e.g. for the date '16/01/2012'.

          But if this is the entire problem, I do not know.

          • Date problem
            Henric Cronström

             

            The problem is probably in the SQL database. You use MAKEDATE(B2GEAN,B2GEME,B2GEDI) in the Details table and MAKEDATE(FVCOEJ,FVGEME,FVGEDI) in the Invoice table. That should work.

             

            So I suspect that the invoice table (S65FCEEB.APLREMGEFI.FV) isn’t updated correctly. Check if the fields FVCOEJ,FVGEME,FVGEDI in it have correct values or if there are records missing in it (records that have corresponding values in the Details table).

             

            • Re: Date problem
              Fernando Toledo

              I think you might be missing some dates. Try concatenating the dates from both tables before finding max and min values!

               

              And try to use the PEEK() function in this case. FieldValue() is used in chart expressions.

               

               

              CALENDARIO_TEMP:
              LOAD distinct
                        FECHA
              RESIDENT FACTURAS;
              CONCATENATE LOAD distinct
                        FECHA
              RESIDENT ALBARAN_DETALLE_VENTA;
              
              
              CALENDARIO_TEMP_II:
              LOAD
                        Max(FECHA) AS DateMax,
                        Min(FECHA) AS DateMin
              RESIDENT CALENDARIO_TEMP;
              
              
              LET vMaxDate = FieldValue('DateMax', 1);
              LET vMinDate = FieldValue('DateMin', 1) -1; 
              
              
              DROP TABLES CALENDARIO_TEMP,CALENDARIO_TEMP_II;
              
              
              • Date problem

                Hello everyone and thanks for your answers.

                 

                Miguel, the proposed solution returns the same values.

                 

                Henric, I think the same as you but developers say the management application that everything is correct, the details corresponding fields are 0 except FVCOEJ is right.

                 

                Fernando, your solution seems to work.

                 

                 

                I have to say that today, miraculously, without making any changes, the document seems to work correctly.

                 

                 

                greetings