topic Re: Latest record in QlikView

Latest record

Nagaraju_KCS — Mon, 09 Dec 2013 06:59:02 GMT

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

Re: Latest record

tresB — Mon, 09 Dec 2013 07:15:13 GMT

May be like attached sample?

Re: Latest record

senpradip007 — Mon, 09 Dec 2013 07:45:52 GMT

try this:

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;

Re: Latest record

Mon, 09 Dec 2013 09:30:45 GMT

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;