Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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;
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