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

[#1]

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;

[maxgro · Accepted solution]

may be

NoConcatenate

load

ACCNT_ID,

MIN(ORD_YR) as First_Sale_Yr,

FirstSortedValue(SALES_AMT, ORD_YR),

MAX(ORD_YR) as Last_Sale_Yr,

FirstSortedValue(SALES_AMT, -ORD_YR)

Resident ORD_SUMM

Group By ACCNT_ID;

[maxgro]

or this?

table:

NoConcatenate

load

ACCNT_ID,

ORD_YR as First_Sale_Yr,

SALES_AMT as First_Sale_Amt

Resident ORD_SUMM

where not peek(ACCNT_ID)=ACCNT_ID

order by ACCNT_ID, ORD_YR;

join (table)

load

ACCNT_ID,

ORD_YR as Last_Sale_Yr,

SALES_AMT as Last_Sale_Amt

Resident ORD_SUMM

where not peek(ACCNT_ID)=ACCNT_ID

order by ACCNT_ID, ORD_YR desc;

[#4]

Thank You Very much .. Worked well!

I have been using previous() function for a while and this solution is going to reduce my script substantially

Thanks again

AP