Finding Max by Month

[#1]

Hello All,

I am newbie to Qlikview and need help in getting resolution for below scenario.

I have a table with following structure and data and I need to find latest record by Month.

Date	Month	ID	Name	Sysid
7/10/2015 12:38	Jul-15	8	ABC1	5
7/23/2015 9:12	Jul-15	9	ABC2	6
8/8/2015 19:29	Aug-15	14	LMN	7
9/29/2015 11:34	Sep-15	17	XYZ1	8
9/29/2015 16:20	Sep-15	19	XYZ2	9

Expected Output

Month	Name
Jul-15	ABC2
Aug-15	LMN
Sep-15	XYZ2

I wanted to find maximum by each month based on Date and Sysid fields.

I have gone through few of the posts which uses Max & Aggr functions however I am not able to achieve above expected output.

Could anyone pls help on this?

Regards

[sunny_talwar · Accepted solution]

Script

Table:

LOAD *,

  Date(MonthStart(Date), 'MMM-YYYY') as Month;

LOAD * INLINE [

    Date, ID, Name, Sysid

    7/10/2015 12:38, 8, ABC1, 5

    7/23/2015 9:12, 9, ABC2, 6

    8/8/2015 19:29, 14, LMN, 7

    9/29/2015 11:34, 17, XYZ1, 8

    9/29/2015 16:20, 19, XYZ2, 9

];

[#3]

Thanks Sunny. By using MonthStart while loading has worked as expected.

Thanks others as well who have responded with their suggestions.

Regards,

Nikhil