topic Re: group By on MaxDate in QlikView

group By on MaxDate

vijayit43 — Tue, 28 Apr 2015 12:27:51 GMT

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

Re: group By on MaxDate

ankitaag — Tue, 28 Apr 2015 12:30:03 GMT

Hi,

You want this result at frontend or in the script?

Re: group By on MaxDate

Tue, 28 Apr 2015 12:35:49 GMT

Hi vij,

please see attached, should help with this

Joe

Re: group By on MaxDate

anbu1984 — Tue, 28 Apr 2015 12:42:24 GMT

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 ];

Re: group By on MaxDate

robert_mika — Tue, 28 Apr 2015 13:17:48 GMT

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;

Re: group By on MaxDate

vijayit43 — Wed, 29 Apr 2015 06:19:59 GMT

We want result in script..

Re: group By on MaxDate

Wed, 29 Apr 2015 06:31:46 GMT

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;

Re: group By on MaxDate

ankitaag — Wed, 29 Apr 2015 08:10:31 GMT

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