Latest record

[qv_testing]

Hi, how to calculate latest record

mat      Item       Year       Month     Toal

AAAA   A01          2012     8               50

AAAA   A01          2012     11             12

AAAA   A01          2012     12               21

MMM   A04         2013      9               90

MMM   A04          2013     8               50

I want to return

AAA    A01    2012     12   21

MMM   A04    2013     9     90

Thanks in Advance

[tresesco]

May be like attached sample?

[senpradip007]

try this:

A:

LOAD * Inline [

mat, Item, Year, Month, Toal

AAAA, A01, 2012, 8, 50

AAAA, A01, 2012, 11, 12

AAAA, A01, 2012, 12, 21

MMM, A04, 2013, 9, 90

MMM, A04, 2013, 8, 50

];

Inner Join(A)

LOAD
Max(Year) AS YearMaxim,
Max(Month) As Month

Resident A
Group By mat;

[#4]

hi

this is according to your requirement.

tab1:

LOAD * INLINE [

    mat, Item, Year, Month, Toal

    AAAA, A01, 2012, 8, 50

    AAAA, A01, 2012, 11, 12

    AAAA, A01, 2012, 12, 21

    MMM, A04, 2013, 9, 90

    MMM, A04, 2013, 8, 50

];

output:

load

mat as mat_new,

Item as Item_new,

Year as Year_new,

max(Month) as Month_new,

FirstSortedValue(Toal,-Month) as Total_new

Resident tab1

Group by mat,Item,Year;

DROP Table tab1;