How to get the last three orders of a customer

[#1]

I have a table containing the following fields:

customer_id      order_id      order_date

Now I want to create a table containing only the last three orders of a customer and ignore all the other orders. Could anyone please tell me how to do it?

[#2]

Hi,

Can u Upload Sample data ??

[PrashantSangle]

Hi,

You can use Rank()

Regards

[#4]

Hi,

use the calculated dimension for the Customer

as

=Aggr(if(Rank(Date,1,1)<=1,Customer),Customer,Orde_id)

and Dimension as

=Order_id

and Expression as

=Order_Date

Regards,

Vivek

[Anonymous]

Try the following:

let vlast = peek('Row ID', -3, 'T1');

[Last Three]:

Load *

Resident T1

where [Row ID] >= $(vlast);

If you need to show last three records with front-end, check file I attached

[anbu1984]

Check this

[#7]

HI,

if your order_id's run in sequential order, a quick and easy way would be to apply a dimension limit to the order_id field (ie. show the largest 3)

regards

[Anonymous]

T1:

Load * Inline [

customer_id,order_id,order_date

1,1,1/1/2014

1,2,1/2/2014

1,3,1/3/2014

1,4,1/4/2014

2,1,1/1/2014

2,2,1/2/2014

2,3,1/3/2014

2,4,1/4/2014

2,5,1/4/2014];

T2:

Load

customer_id,

Max(order_id) as max_order

Resident T1

group by customer_id;

Join(T2)

Load * Resident T1;

Drop Table T1;

T3:

Load *, 'as' as source

Resident T2

where order_id > max_order -3;

Drop table T2;

[Anonymous]

Hi oil,

Please see below script.

Test:

LOAD * INLINE [

    customer_id,order_id,order_date

    c1, o1, 01-01-2014

    c2, o2, 05-01-2014

    c3, o3, 10-01-2014

    c4, o4, 11-01-2014

    c6, o5, 01-02-2014

];

NoConcatenate

test1:

load * Resident Test

order by order_date;

DROP Table Test;

let vMaxOD = peek('order_date',2,test1);

set vDate = $(vMaxOD);

NoConcatenate

test2:

load * Resident test1

where order_date>= '$(vDate)';

DROP Table test1;

thanks