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

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

Tue, 06 May 2014 19:58:52 GMT

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,

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

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

maxgro — Tue, 06 May 2014 20:12:31 GMT

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;

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

maxgro — Tue, 06 May 2014 20:19:23 GMT

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;

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

Tue, 06 May 2014 20:38:00 GMT

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