Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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;

1 Solution

Accepted Solutions
MVP
MVP

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

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;

3 Replies
MVP
MVP

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

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;

MVP
MVP

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

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;

Not applicable

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

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

Community Browser