In load script: aggr & rank

[wanyunyang]

I have a table with columns: OrderType, OrderNo, ShipmentDays. I would like to rank ShipmentDays within OrderType group.

e.g.

OrderType, OrderNo, ShipmentDays, Rank

Fruit,            A,                   3,                               1

Veg,              B,                   2,                               1

Veg,              A,                   3,                               2

How should I achieve this? Thanks in advance!

[sunny_talwar]

Try this

Table:
LOAD * INLINE [
    OrderType, OrderNo, ShipmentDays
    Fruit, A, 3
    Veg, B, 2
    Veg, A, 3
];

FinalTable:
LOAD *,
	 If(OrderType = Previous(OrderType), RangeSum(Peek('Rank'), 1), 1) as Rank
Resident Table
Order By OrderType;

DROP Table Table;

[wanyunyang]

Hi Sunny,

Thanks for helping! Do you mean Order By ShipmentDays? I was trying to rank ShipmentDays within each OrderType.

[sunny_talwar]

Then add that as well

FinalTable:
LOAD *,
	 If(OrderType = Previous(OrderType), RangeSum(Peek('Rank'), 1), 1) as Rank
Resident Table
Order By OrderType, ShipmentDays;