Load elements of field based on max date

[#1]

Hello,

hopefully this is a simple question for the community.

I have a table that has the following three fields and sample data as below.

%POSItemId	POSGLEffectiveDateTEMP	%GLAccount
A	02.01.2009	999
A	02.01.2009	666
A	02.01.2008	777
A	02.01.2008	555
B	02.01.2009	333
B	02.01.2008	222

Would like it to look like this after the load:

%POSItemId	POSGLEffectiveDateTEMP	%GLAccount
A	02.01.2009	999
A	02.01.2009	666
B	02.01.2009	333

To reduce the table size on load I'm trying to only load in the maximum date for each %POSItemId. However, there can be multiple GLAccounts associated with each %POSItemId.

I've been trying to do this, see below, but hasn't worked.

D_POSItemPLUGL:

LOAD POS_ITEM_ID as %POSItemId

    ,GL_ACCOUNT_ID as %GLAccountId

  ,Date(Floor(EFFECTIVE_DATE))

  as POSGLEffectiveDateTEMP 

FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLPOSITEMPLUGL.qvd] (qvd);

Left Join (D_POSItemPLUGL)

LOAD GL_ACCOUNT_ID as %GLAccountId

    ,GL_ACCOUNT_NUMBER as %GLAccountNumber

FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLGLACCOUNT.qvd] (qvd);

Drop Field %GLAccountId;

Left Join (POSLinkTable)

LOAD * Resident D_POSItemPLUGL

Where %POSItemId <> Previous(%POSItemId)

Order by %POSItemId, POSGLEffectiveDateTEMP DESC;

Drop Table D_POSItemPLUGL;

Any advice would be greatly appreciated.

[Gysbert_Wassenaar · Accepted solution]

Try this:

D_POSItemPLUGL:
LOAD
     POS_ITEM_ID as %POSItemId
    ,GL_ACCOUNT_ID as %GLAccountId
    ,Date(Floor(EFFECTIVE_DATE)) as POSGLEffectiveDateTEMP
FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLPOSITEMPLUGL.qvd] (qvd);

Right Join

LOAD
     %POSItemId,
     max(POSGLEffectiveDateTEMP) as POSGLEffectiveDateTEMP
Resident D_POSItemPLUGL
Group By %POSItemId;

[#3]

Worked like a charm. Thank you for the clear and fast response, very helpful.

Jon