1 Reply Latest reply: May 15, 2014 10:10 AM by Data Kruncher RSS

    MAX date value of a table

    joe.lovati _

      I'm trying to filter records from a table at the max date of a field.  Example:

       

      Field A:           Date:

      A                   01/01/2011

      A                   02/01/2011

      A                   04/10/2011                  

      B                   04/01/2011

      B                   04/11/2011

      C                   01/01/2011

      C                   02/01/2011

      C                   02/01/2011

      C                   03/01/2011

       

      Should return:

      A                   04/10/2011

      B                   04/11/2011

      C                   03/01/2011

        • MAX date value of a table
          Data Kruncher

          Hi Joe,

           

          The only solution I can envision for this would require two passes.

           

          In the first pass, have a model with a summary that produces a simple list of the key alpha values. Your example would have only three rows: A, B, and C.

           

          Now sort the table first by the alpha field (ascending), then also by the date (descending).

           

          Build project exports that export both the table, as sorted, and the summary. Run the project and then save and close the model and project files.

           

          Now in a new model, open the summary export as a database source. Add an external lookup to bring in the date from previously exported table. Monarch will import the first instance, in this case the most recent date.

           

          That should do it. Thoughts?