Unique Order Number

[#1]

Hi:

I have got Four Columns :

Item_Number

Vendor_Number

Order_Date

Order_Number

There could be several Vedors for each Item_Number and Several Order_Dates related to each vendor and item,

my report requires picking up the latest order number for each vedor related to a part based on the latest order date.

Appreciate your help.

thanks

Item_Number	Vendor_Number	Order_Date	Order_Number
A	666	20120101	9
A	666	20130101	10
A	777	20120101	11
A	777	20130101	12
B	666	20120101	80
B	666	20130101	44
B	999	20120101	22
B	999	20130101	69
Required
Item_Number	Vendor	Max Date	Latest PO   (Based on Max Date & Vedor & Item Number)
A	666	20130101	10
A	777	20130101	12
B	666	20130101	44
B	999	20130101	69

[MayilVahanan]

HI

Try like this

Test:

LOAD * INLINE [

    Item_Number, Vendor_Number, Order_Date, Order_Number

    A, 666, 20120101, 9

    A, 666, 20130101, 10

    A, 777, 20120101, 11

    A, 777, 20130101, 12

    B, 666, 20120101, 80

    B, 666, 20130101, 44

    B, 999, 20120101, 22

    B, 999, 20130101, 69

];

OrderNo:

LOAD

Item_Number,

Vendor_Number,

Max(Order_Date) AS OrderDate,

FirstSortedValue(Order_Number,-Order_Date) AS OrderNumber

Resident Test

Group by Item_Number,Vendor_Number;

DROP Table Test;

[#3]

Hi,

The firstsoretedvalue function should work for this. Try something like:

firstsortedvalue(Order_Number, - Order_Date)

Best,

Matt