Skip to main content
Question

Bigger version but smaller date

  • August 24, 2017
  • 12 replies
  • 10 views
K
Forum|alt.badge.img

Hello, I have the following table:

Name Version Date

Alpha 1.1 20170304

Alpha 1.1 20180304

Alpha 1.2 20150204

Alpha 1.3 20191004

Beta 1.1 20130304

Beta 1.2 20150404

Beta 1.2 20161204

Beta 1.3 20151004

I would like the following records as an output:

Alpha 1.2 20150204

Beta 1.3 20151004

These are the records who have a bigger version but theirs dates are inferior to the max date of the previous version grouped by name. How can I find these records automatically? I have more than 100 000 records to analyze. Thanks!

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

12 replies

courtney_m
Contributor
Forum|alt.badge.img+19
  • courtney_mContributor
  • Contributor
  • 127 replies
  • August 24, 2017

You can use a Sorter, then a DuplicateFilter. Set up the sorter to sort first by Name, then by Ascending date, then by descending version, as shown below:

Have that result lead into the DuplicateFilter where Key Attribute is Name, and Input is Ordered is set to Yes.

courtney_m
Contributor
Forum|alt.badge.img+19
  • courtney_mContributor
  • Contributor
  • 127 replies
  • August 24, 2017

Nice brain teaser!

courtney_m
Contributor
Forum|alt.badge.img+19
  • courtney_mContributor
  • Contributor
  • 127 replies
  • August 24, 2017

Nice brain teaser!

Okay, someone else beat me to it!
tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • 311 replies
  • August 24, 2017

I believe this will do it. There may be other ways...

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • 311 replies
  • August 24, 2017

I believe this will do it. There may be other ways...

You may be able to replace the AttributeFilter and Testers with the TestFilter.

 

 

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • 311 replies
  • August 24, 2017

I believe this will do it. There may be other ways...

I've replaced the original image with a new version that has an extra AttributeManager to give the correct results.

 

erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • August 24, 2017

This was a fun challenge and I believe it can be fairly simple:

First sort the data on Name, Version and Date.

Then create an attribute for the Name, Date and Version of the previous record.

Then test if the previous Name = the Name and the previous Date > the current Date.

The output is:

erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • August 24, 2017

This was a fun challenge and I believe it can be fairly simple:

First sort the data on Name, Version and Date.

Then create an attribute for the Name, Date and Version of the previous record.

Then test if the previous Name = the Name and the previous Date > the current Date.

The output is:

Here is the workspace: test.fmw

 

 

takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • August 25, 2017

Do you need to compare each date with the latest date in all the previous versions belonging to the same group? For example, if the date of "Alpha 1.3" was 20161004 (later than "Alpha 1.2, 20150204" but earlier than "Alpha 1.1, 20180304"), should the "Alpha 1.3" be detected?

If "No", @erik_jan's solution would work as expected.

If "Yes", the workflow should be modified like this.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • 311 replies
  • August 25, 2017

Do you need to compare each date with the latest date in all the previous versions belonging to the same group? For example, if the date of "Alpha 1.3" was 20161004 (later than "Alpha 1.2, 20150204" but earlier than "Alpha 1.1, 20180304"), should the "Alpha 1.3" be detected?

If "No", @erik_jan's solution would work as expected.

If "Yes", the workflow should be modified like this.

I was just thinking about that same scenario. Are you just looking for one date out of sequence (e.g. 2013, 2014, 2012, 2015, 2016) or many (e.g. 2013, 2010, 2014, 2012, 2015 or 2013, 2009, 2010, 2011, 2014, 2012, 2015)?

 

takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • August 25, 2017

Do you need to compare each date with the latest date in all the previous versions belonging to the same group? For example, if the date of "Alpha 1.3" was 20161004 (later than "Alpha 1.2, 20150204" but earlier than "Alpha 1.1, 20180304"), should the "Alpha 1.3" be detected?

If "No", @erik_jan's solution would work as expected.

If "Yes", the workflow should be modified like this.

0684Q00000ArJpQQAV.png

The "_prev_update" indicates the maximum in the dates of all the previous versions belonging to the same Name group, regardless of the sequence of dates.

 

In addition, an InlineQuerier with this SQL statement could also work.

 

select x.*
from (select a.*, max(b.Date) as _prev_update
    from source as a
    inner join source as b on b.Name = a.Name and b.Version < a.Version
    group by a.Name, a.Version, a.Date) as x
where x.Date < x._prev_update order by x.Name, x.Version

 

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
mark2atsafe
Safer
Forum|alt.badge.img+56
  • mark2atsafeSafer
  • Safer
  • 2554 replies
  • August 25, 2017
As you tagged it, this would be perfect for the Sorter to do - if only it handled dates a bit better. That's filed as PR#20821 and I am going to link this thread to the request because it's a good example.

 

FME Evangelist to the Rich and Famous!!

Community Stats

32,093
Posts
121,876
Replies
39,733
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.