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

[OllyInMunich]

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).

HTH,

Olly

[Nick Osdale-Popa]

If you make a calculated field and convert your dates to serial numbers as provided in the tips in this thread (http://www.monarchforums.com/showthread.php?t=1261), you should be able to create a summary easily enough without having to use a 2-pass process.

[BaWahoo]

Thank you Olly and Nick for the suggestions,

I went back and did the sorting in my query before the report is exported. Then applied the "duplicated rows; one row only" and it worked perfect. I got to work with 1 pass. Thanks again.