topic Unique Order Number in QlikView

Unique Order Number

Sat, 21 Sep 2013 16:32:39 GMT

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 — Sat, 21 Sep 2013 22:20:34 GMT

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;

Sat, 21 Sep 2013 22:51:55 GMT

Hi,

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

firstsortedvalue(Order_Number, - Order_Date)

Best,

Matt