Latest Date Issue

[#1]

Hi,

I want to load only rows which is having the latest Updation date.

Name	Number	Address	Updation Date
John	12	US	10-Jan-13
John	12	UK	15-Jan-13
Mary	14	UK	10-Jan-13
Wilson	15	India	15-Feb-13
Wilson	15	UAE	18-Feb-13
Martin	16	UAE	15-Feb-13
Mariya	17	China	18-Feb-13

In detail i want to load the Adress of employee based on the latest Updation Date.( For John Adress should be UK)

Thanks in Advance.

[#2 · Accepted solution]

load name,

    max(date(updationdate)) as maxdate,

    FirstSortedValue(address,-updationdate) as newaddress

    Group by name;

LOAD name,

     number,

     address,

     updationdate

FROM

(ooxml, embedded labels, table is Sheet1);

then output like this--

name	newaddress	maxdate
mary	uk	1/10/2013
john	uk	1/15/2013
martin	uae	2/15/2013
mariya	china	2/18/2013
wilson	uae	2/18/2013

[#3]

HI Yaseen,

Use groub by to achive the result

a:

LOAD * INLINE [

        Name, Number, Address, Updation_Date

    John, 12, US, 10-Jan-13

    John, 12, UK, 15-Jan-13

    Mary, 14, UK, 10-Jan-13

    Wilson, 15, India, 15-Feb-13

    Wilson, 15, UAE, 18-Feb-13

    Martin, 16, UAE, 15-Feb-13

    Mariya, 17, China, 18-Feb-13

];

b:

NoConcatenate

load  Name, Number, Address,date(Date#(trim(Updation_Date),'DD-MMM-YY')) as Updation_Date  Resident a;

c:

LOAD date(max(Updation_Date)) as date,Address,Name Resident b Group by Address,Name;

drop Table a,b;

[SunilChauhan]

In Dimension,

Name ,Number and Updationdate and in

Expression

firstsortedVale(Adress,Updationdate)

Hope this helps

[#5]

Thanks a ton Vishwa, its working.

I have doubt plz clear me what is the meaning of "-updationdate" this.

Regards,

yaseen

[#6]

Hello,

I think we can use FirstSortedValue() in the scripting only not in the charts.

Regards,

yaseen

[SunilChauhan]

Hi,

i think we can use firstsortedvalue in expression.to check type firstsortedValue() in expression.

if it show blue color and popup arguments it means it should use in expression otherwise not.

Hope this helps

[#8]

Hello,

I have checked it is not working.

[SunilChauhan]

we can use first sorted value in expressions also

see the example attached