13 Replies Latest reply: Dec 11, 2014 7:43 AM by Anand Chouhan RSS

    Load Field with MAX date

      Hi need help,

       

      Example;

       

       

      TableA:

       

      Field Header:         Order     |         Date     |         Customer       |    

      Field Content:          A                12/11/2014               John                                    

                                      A                12/10/2014                John                                    

                                      B                12/07/2014                Paul                                   

                                      B                12/05/2014                Paul                                     

       

      What I need is a load statement which would give me as an output the Order with the latest Date.

       

      Expected results:

      Order           Date

      A               12/11/2014

      B               12/07/2014

        • Re: Load Field with MAX date
          Anand Chouhan

          Hi,

           

          Try load load the

           

          Raw:
          LOAD Order, Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date, Customer;
          LOAD * INLINE [
              Order, Date, Customer
              A, 12/11/2014, John
              A, 12/10/2014, John
              B, 12/07/2014, Paul
              B, 12/05/2014, Paul  ];   

           

          Result:
          LOAD
          Order,
          Date(Max(Date),'MM/DD/YYYY') as MaxDate
          Resident Raw Group By Order;

           

          By use of the Max function and aggregate the records in the load script

          Order           Date

          A               12/11/2014

          B               12/07/2014

           

          Regards

          Anand

            • Re: Load Field with MAX date

              Hi,

               

              what I need is something like this in one load statement:

               

              Example;

               

               

              TableA:

               

              Field Header:         Order     |         Date     |         Customer       |    

              Field Content:          A                12/11/2014               John                                   

                                              A                12/10/2014                John                                    

                                              B                12/07/2014                Paul                                   

                                              B                12/05/2014                Paul                                    

               

              What I need is a load statement which would give me as an output the Order with the latest Date.

               


              LOAD
                Order,

                Customer,

                max(DATE)

              FROM
              TableA   (
              qvd)

               

                

              Expected results:

               

              Order           Date

               

              A               12/11/2014

               

              B               12/07/2014

               

               

               

               

               

              Actual results:

               

               

               

              Order        Date

               

              A                12/11/2014

               

               

               

                • Re: Load Field with MAX date
                  Serhan Celebi

                  group by Order;

                   

                  BR

                   

                  Serhan

                    • Re: Load Field with MAX date

                      and if I want as a result

                      Expected results:

                       

                      Order           Date               Customer

                       

                      A               12/11/2014               John

                       

                      B               12/07/2014               Paul

                       

                      then just --> group by Order, Customer;  ?

                        • Re: Load Field with MAX date
                          Serhan Celebi

                          exactly.

                           

                          BR

                           

                          Serhan

                            • Re: Load Field with MAX date

                              okay so far so good (and here the tricky part) so far  I have been.

                               

                              But now imagine the customer name has changed as well

                               

                              Field Header:         Order     |         Date     |         Customer       |    

                              Field Content:          A                12/11/2014               John                                  

                                                              A                12/10/2014                John                                   

                                                              B                12/07/2014                Marc                                   

                                                              B                12/05/2014                Paul                                    

                              The output should have just:

                              Order           Date               Customer

                               

                              A               12/11/2014               John

                               

                              B               12/07/2014              Marc

                               

                               

                              then just --> group by Order, Customer;  would not work correct? As it would consider Paul as an separate line.

                            • Re: Load Field with MAX date
                              neetha P

                              yes that's right

                          • Re: Load Field with MAX date
                            Anand Chouhan

                            Hi,

                             

                            Let me know in the result you want only single row that is the requirement

                             

                            Order        Date

                            A                12/11/2014


                            Regards

                            Anand

                              • Re: Load Field with MAX date

                                okay so far so good (and here the tricky part) so far  I have been.

                                 

                                But now imagine the customer name has changed as well

                                 

                                Field Header:         Order     |         Date     |         Customer       |    

                                Field Content:          A                12/11/2014               John                                 

                                                                A                12/10/2014                John                                  

                                                                B                12/07/2014                Marc                                   

                                                                B                12/05/2014                Paul                                   

                                The output should have just:

                                Order           Date               Customer

                                 

                                A               12/11/2014               John

                                 

                                B               12/07/2014              Marc

                                 

                                 

                                then just --> group by Order, Customer;  would not work correct? As it would consider Paul as an separate line.