Load elements of field based on max date

Mon, 26 Jan 2026 18:19:17 GMT

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.

Re: Load elements of field based on max date

Gysbert_Wassenaar — Thu, 04 Jun 2015 15:40:30 GMT

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;

Re: Load elements of field based on max date

Thu, 04 Jun 2015 15:53:53 GMT

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

Jon

topic Load elements of field based on max date in QlikView

Load elements of field based on max date

Re: Load elements of field based on max date

Re: Load elements of field based on max date