Retrieve Max Date at Grouped Level - Group By?

[matt_crowther]

I hope someone can assist:

I'm looking at a table called 'Policy' that lists Policy numbers and renewal dates - ie one policy number can have multiple renewal dates within the table eg:

Policy Renewal

A 01/01/2008

B 01/02/2009

A 01/01/2009

B 01/02/2009

A 01/01/2010

C 01/03/2010

So what I need to do is a resident load perhaps that will return 1 row per Policy and the maximum Renewal date for that Policy - ie the most recent so I can link that back to Policy for use within the front end. I've tried using a Resident Load and Group By:

ReportDate:

Load Policy.ParentPolicyId as RPParentPolicyID,

max(Policy.EndDate) as ReportDate

Resident Policy Group By RPParentPolicyID;

But that isn't working as the script falls over.

I can return the relevant date in a table grouped at the Policy Level in the front end but that isn't suitable unfortunately.

Many thanks.

[justinasp]

Hi, Matt

you shoud use the original field name for grouping:

Group By Policy.ParentPolicyId; 

Good luck,

Justinas

[wizardo]

hi

you ware using the new name "RPParentPolicyID" that you gave to the field "Policy.ParentPolicyId"

as the field for your GROUP BY clause

the new name will only be known after the load statement finishes so the load itself still knows the field by its old name (Policy.ParentPolicyId)

change your code to this:

// right join

//id suggest unmarking the above join to make sure only records with the MAX(DATE) stayes at the end.

ReportDate:

Load Policy.ParentPolicyId as RPParentPolicyID,

max(Policy.EndDate) as ReportDate

Resident Policy Group By Policy.ParentPolicyId;

this should work

Mansyno

[#4]

Hello. Look at this example. Maybe it will halp you.

Table1:
load * inline
[Policy, Renewal
A,01/01/2008
B,01/02/2009
A,01/01/2009
B,01/02/2009
A,01/01/2010
C,01/03/2010];

Table2:
load distinct
Policy
resident Table1;

left join (Table2) load
Policy,
max(Renewal) as Renewal
resident Table1
group by Policy;

drop table Table1;

[#5]

Hello. Look at this example. Maybe it will halp you.

Table1:
load * inline
[Policy, Renewal
A,01/01/2008
B,01/02/2009
A,01/01/2009
B,01/02/2009
A,01/01/2010
C,01/03/2010];

Table2:
load distinct
Policy
resident Table1;

left join (Table2) load
Policy,
max(Renewal) as Renewal
resident Table1
group by Policy;

drop table Table1;