2 Replies Latest reply: Jun 4, 2015 11:53 AM by Jon Chuma RSS

    Load elements of field based on max date

    Jon Chuma

      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.

      %POSItemIdPOSGLEffectiveDateTEMP%GLAccount
      A02.01.2009999
      A02.01.2009666
      A02.01.2008777
      A02.01.2008555
      B02.01.2009333
      B02.01.2008222

       

      Would like it to look like this after the load:

      %POSItemIdPOSGLEffectiveDateTEMP%GLAccount
      A02.01.2009999
      A02.01.2009666
      B02.01.2009333

       

      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.