Using MAX in a loader script

[paulm]

Hi,

I am sure this will be a silly question, but I am struggling to get by it so wondering if I could ask some help please.

I have record that has multiple records associated with it called SAs. What I need to do is find latest one that is assocaited with the main record based on date, which is less than the creation date. Any help greatly appreciated.

Thanks,

Paul

TEST:
LOAD
 CI_WF_PROC_MPRN_SA.LATEST_SA_ID AS TEST.LATEST_SA_ID,
 CI_WF_PROC_MPRN_SA.CRE_DTTM as TEST.CRE_DTTM,
 CI_WF_PROC_MPRN_SA.MPRN AS TEST.MPRN,
 MPRN AS TEST.MPRN2,
 max(CI_WF_PROC_MPRN_SA.LATEST_START_DATE) AS TEST.LATEST_START_DATE,
 CI_WF_PROC_MPRN_SA.LATEST_END_DATE AS TEST.LATEST_END_DATE
 //STL.AccountID as ACCT_ID,

RESIDENT CI_WF_PROC_MPRN_SA
WHERE date(CI_WF_PROC_MPRN_SA.CRE_DTTM) > date(CI_WF_PROC_MPRN_SA.LATEST_START_DATE, 'DD/MM/YYYY')
group by CI_WF_PROC_MPRN_SA.LATEST_SA_ID,MPRN;

[#2 · Accepted solution]

Are you getting an error? It looks like your problem is that some fields aren't aggregated. When using the group by, every field must either be in the group by or use an aggregate function. Try:

TEST:
LOAD
 CI_WF_PROC_MPRN_SA.LATEST_SA_ID AS TEST.LATEST_SA_ID,
 MPRN AS TEST.MPRN2,
 max(CI_WF_PROC_MPRN_SA.LATEST_START_DATE) AS TEST.LATEST_START_DATE,
RESIDENT CI_WF_PROC_MPRN_SA
WHERE date(CI_WF_PROC_MPRN_SA.CRE_DTTM) >
 date(CI_WF_PROC_MPRN_SA.LATEST_START_DATE, 'DD/MM/YYYY')
group by CI_WF_PROC_MPRN_SA.LATEST_SA_ID,MPRN;

What you may need to do is load all of your fields in one load. Then do a join using this load. That way, you get all of your fields you want and then have a Max associated with each record.