group By on MaxDate

[vijayit43]

Hi, My data type is

User	Date	Type
User1	1/4/2015	A
User1	4/4/2015	B
User1	2/4/2015	C
User2	1/4/2015	A
User2	3/4/2015	B
User2	5/4/2015	C

we want result this type (UserWise  Max(date)-> Type)

User	Type
User1	B
User2	C

[ankitaag]

Hi,

You want this result at frontend or in the script?

[#3]

Hi vij,

please see attached, should help with this

Joe

[anbu1984]

Load User,FirstSortedValue(Type,-Date) As Type Group By User;
Load User,Date#(Date,'DD/MM/YYYY') As Date,Type Inline [
User,Date,Type
User1,1/4/2015,A
User1,4/4/2015,B
User1,2/4/2015,C
User2,1/4/2015,A
User2,3/4/2015,B
User2,5/4/2015,C ];

[robert_mika]

t1:

load * inline

[

User, Date ,Type

User1, 01/04/2015, A

User1 ,04/04/2015, B

User1, 02/04/2015 , C

User2 ,01/04/2015 , A

User2 ,03/04/2015 , B

User2 ,05/04/2015 , C

]

;

t2:

load

User as u,

Date(Date#(Date,'D/M/YYYY')) As Dat,

Type as T

Resident t1;

drop Table t1;

[vijayit43]

We want result in script..

[#7]

Dear,

Use it,

table:

LOAD * INLINE [

User ,Date ,Type

User1, 1/4/2015, A

User1, 4/4/2015, B

User1, 2/4/2015, C

User2, 1/4/2015, A

User2 ,3/4/2015, B

User2, 5/4/2015, C

];

MAX:

LOAD User,

User&max(Date)  as KEY,

max(Date) as MAXDATE

Resident table Group by User ;

Left Join

LOAD

User&num(Date)  as KEY,

Type

Resident table;

DROP Table table;

[ankitaag]

Hi,

Try this script :

T1:

LOAD User,Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date,Type INLINE [

    User, Date, Type

    User1, 1/4/2015, A

    User1, 4/4/2015, B

    User1, 2/4/2015, C

    User2, 1/4/2015, A

    User2, 3/4/2015, B

    User2, 5/4/2015, C

];

Mapping:

Mapping LOAD Date,Type

Resident T1;

NoConcatenate

T2:

Load User,max(Date) as Date

Resident T1

group by User;

drop table T1;

Final:

Load User,Date,ApplyMap('Mapping',Date) as Type

Resident T2;

drop table T2;

For further reference PFA the application