3 Replies Latest reply: May 15, 2014 10:02 AM by BaWahoo _ RSS

    LAST DATE valued

    BaWahoo _

      How can I use the Filter or Calculated Field functions to filter only the LAST DATE record for the example below?


      I only want to see the row with 2007-09-10 and 2008-05-14 displayed after the filter is applied. So only the LAST DATE valued should be displayed.  Thank you.



      NO1     NO2           DATE POST

      383170     650083170001     2007-07-21

      383170     650083170001     2007-07-25

      383170     650083170001     2007-09-10

      152774     950152774006     2008-03-19

      152774     950152774006     2008-03-29

      152774     950152774006     2008-05-09

      152774     950152774006     2008-05-14

        • LAST DATE valued
          Olly Bond

          Hello BaWahoo,


          If your data was sorted in descending date order, it would be easy, just set up a filter in the table, and in the advanced tab of the filter definition dialog select "duplicated rows; one row only". I tried sorting your sample in descending date order but the filter is applied to the data by Monarch before the sort is, so that got the wrong result.


          In two passes, it's easy enough and as ever, there's more than one way to solve it in Monarch. Easiest is to export the table as fixed width text, with a sort order applied to put the dates in descending order, and then the second model opens this export and applies the advanced filter above.


          If it wasn't a date you were filtering on but a numeric field then you could use a summary to do it in one pass. If you don't mind slightly ugly dates then you can do it this way:


          Trap the date or convert it using calculated fields so that it is a string of the form "YYYY-MM-DD". Create a calculated field from this called NumDate, defined as val(strip(Date);"-") - this gives you a number YYYYMMDD. Then all you need to do is create a summary where the measure is Max(NumDate).