Skip to main content
Question

Keeping highest date

  • January 13, 2021
  • 3 replies
  • 148 views
joe.fme
Contributor
Forum|alt.badge.img+6

Hi all,

I'm trying to keep the highest date in a range, and each of these has the same reference number. For example - "Reference Number 1 has 8 dates and we need to keep the highest date for Reference 1", but repeat a few thousand times.

 

I've had a method which worked before using Adjacent Attributes and sorting, but it doesn't seem to be working here. The dates are input in the %Y%m%d format (ie. 20120301) which I had hoped would make sorting easier.

 

I didn't know, after a long search and trying methods out, if anyone had any pointers on how to achieve this highest date business?

 

Thank you

    3 replies

    nielsgerrits
    VIP
    Forum|alt.badge.img+60
    • nielsgerritsVIP
    • 2938 replies
    • January 13, 2021

    StatisticsCalculator, group by Reference Number, Analyze date field (%y%m%d), Maximum Attribute is highest date. Complete outputport.

    It should also work in the Excel way, with sorting and adjacent attributes. This is probably a less memory intense solution. But the StatisticCalculator is easier to configure.

    Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
    ebygomm
    Influencer
    Forum|alt.badge.img+44
    • ebygommInfluencer
    • Influencer
    • 3427 replies
    • January 13, 2021

    Sorting in reverse order and then a duplicate filter should work here. The duplicate filter will output the feature with the highest date for each reference number from the unique port

    joe.fme
    Contributor
    Forum|alt.badge.img+6
    • joe.fmeContributor
    • Author
    • Contributor
    • 40 replies
    • January 14, 2021

    StatisticsCalculator, group by Reference Number, Analyze date field (%y%m%d), Maximum Attribute is highest date. Complete outputport.

    It should also work in the Excel way, with sorting and adjacent attributes. This is probably a less memory intense solution. But the StatisticCalculator is easier to configure.

    Edit - just seen it in the "Complete" output port. Ignore my below comment, I'm a numpty!

     

    Hi niels,

     

    I've tried this approach but the correct answers come out of the "Summary" port - and only have the 2 fields, leaving the others out of the output (as shown). Is there something I'm missing?

     

    To make things more complex the dates I've been given have only 1 of them with data in, which means the output is now incomplete - but that's for another time lol


    Community Stats

    32,070
    Posts
    121,778
    Replies
    39,698
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.