Question

Bigger version but smaller date

K
Badge

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!

12 replies

C
Badge

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.

C
Badge

Nice brain teaser!

C
Badge

Nice brain teaser!

Okay, someone else beat me to it!
tim_wood
Rank
Badge +7

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

tim_wood
Rank
Badge +7

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
Rank
Badge +7

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
Rank
Userlevel 2
Badge +16

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
Rank
Userlevel 2
Badge +16

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
Rank
Userlevel 2
Badge +17

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.

tim_wood
Rank
Badge +7

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
Rank
Userlevel 2
Badge +17

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

 

mark2atsafe
Rank
Userlevel 4
Badge +25
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.

 

Reply


Community Stats

28,710
Posts
109,486
Replies
36,558
Members
Terms & ConditionsCookie 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.