Skip to main content
Question

Keeping highest date

joe.fme
Contributor
Forum|alt.badge.img+4

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

    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.

    ebygomm
    Influencer
    Forum|alt.badge.img+31
    • ebygommInfluencer
    • Influencer
    • 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+4
    • joe.fmeContributor
    • Author
    • Contributor
    • January 14, 2021
    nielsgerrits wrote:

    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

    1 Attachments

    Reply


    Helpful Members This Week

    Community Stats

    30,940
    Posts
    117,251
    Replies
    38,742
    Members

    Latest FME

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    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.