3 Replies Latest reply: May 6, 2014 4:38 PM by Aji Paul RSS

    Need help in populating a Summary table - First and Last order

      Experts ,

      I need to populate a create a TABLE while loading (NOT chart). I could use the MIN and MAX function to get the first and last order date. But how to get the corresponding amount?

       

      Thanks,

      AP

       

      [ORD_SUMM]:

      load * inline

      [ACCNT_ID,ORD_YR,SALES_AMT

      ACC_01,2007,800

      ACC_01,2008,1150

      ACC_02,2007,5000

      ACC_02,2008,6000

      ACC_02,2009,4000

      ACC_03,2010,500

      ACC_04,2007,10

      ACC_04,2009,10

      ACC_04,2010,10

      ACC_05,2006,10200

      ACC_05,2007,675

      ACC_05,2008,5350

      ACC_05,2009,500];

       

       

       

      [ACC_FIRST_LAST_ORDERS]:

      load

          ACCNT_ID,

          MIN(ORD_YR) AS First_Sale_Yr,

          <FIRST_SALES_AMT>   // HOW TO GET THE First Sales Amount ?

          MAX(ORD_YR) AS Last_Sale_Yr,

         <LAST_SALES_AMT>   // HOW TO GET THE LastSales Amount ?

      RESIDENT

          ORD_SUMM

      WHERE

          SALES_AMT > 0

      GROUP BY

           ACCNT_ID;