3 Replies Latest reply: Dec 11, 2014 7:48 AM by Anand Chouhan RSS

    Load Scrip pick value 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 Scrip pick value with MAX date
          Aaron Morgan

          Hi Ivan,

           

          If you load your first table without the function, so just as the Date field, and then add a resident load that groups by Order, so:

           

          Load

          Order,

          Date(Max(Date)) as MaxDate

          Resident TableA

          Group by Order;

           

          It should give you what you want.

            • Re: Load Scrip pick value with MAX date

              now I have specified what I need in a more clear sense I guess.

               

              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 Scrip pick value with MAX date
                  Anand Chouhan

                  Hi,

                   

                  By the Date field you can do the same with some table loads as the result

                   

                  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  ];  

                   

                  Expected:
                  LOAD
                  Order, Customer as [Expected Customer],
                  Date(Max(Date),'MM/DD/YYYY') as MaxDate
                  Resident Raw Group By Order,Customer Order By Date desc;

                   

                  LET vMaxDate = Peek('MaxDate',0,'Expected');

                   

                  ActuatResult:
                  NoConcatenate
                  LOAD
                  Order as [Actual Order],[Expected Customer] as [Customer Name],MaxDate as [Expected Date]
                  Resident Expected Where MaxDate = '$(vMaxDate)' and Len([Expected Customer]) > 0;

                   

                  ExpectedOp.png

                  Regards

                  Anand