Hello,
I have two columns in a Excel sheet "Year" and "Range" with more than 2000 rows. I would like to compare those 2 columns and see if the Year is truly in the Range or not. For example:
YearRange
1650
< 1920
1964
1961 - 1970
1970
1961 - 1970
2001
2001 - 2005
1963
1971 - 1980
2015
> 2014
1923
1920 - 1940
1978
1981 - 1990
Thank you in advance for your return
Best Regards
Best answer by markatsafe
@mi You can also jam it all into a single test filter.
In this case the <unfiltered> would also catch a bad date in the ranges. If your confident that your ranges are clean then use the regex proposed by @danullen since they are much simpler.
As an aside, it's kind of hard to test the string functions in the tester - using an AttributeCreator to test that your string functions are returning the correct values before adding them to the TestFilter can help.
Example Workspace (2019): dateranges.fmwt
First, put in a TestFilter with three options: Range begins with <, Range begins with > and Else.
Then put a Tester after each, where:
The first one says Year < @Substring(@Value(range),-4,4)
The second one says Year > @Substring(@Value(range),-4,4)
The third one (Else) says Year >= @Substring(@Value(range),4,4) AND Year <= @Substring(@Value(range),-4,4)
First, put in a TestFilter with three options: Range begins with <, Range begins with > and Else.
Then put a Tester after each, where:
The first one says Year < @Substring(@Value(range),-4,4)
The second one says Year > @Substring(@Value(range),-4,4)
The third one (Else) says Year >= @Substring(@Value(range),4,4) AND Year <= @Substring(@Value(range),-4,4)
Oh, and btw, you might want to look into your overlapping ranges: 1961-1970 and 1970-1980 for example.
@mi You can also jam it all into a single test filter.
In this case the <unfiltered> would also catch a bad date in the ranges. If your confident that your ranges are clean then use the regex proposed by @danullen since they are much simpler.
As an aside, it's kind of hard to test the string functions in the tester - using an AttributeCreator to test that your string functions are returning the correct values before adding them to the TestFilter can help.
Example Workspace (2019): dateranges.fmwt
+28If your range is always going to be one of the three scenarios, and all you care is whether the year passes for fails the range then you can do it with one Tester.
LogicLeft ValueOperatorRight ValueMode(@Value(Range)Begins With<Case InsensitiveAND@Value(Year)<@Right(@Value(Range),4)Automatic) OR (@Value(Range)Begins With>Case InsensitiveAND@Value(Year)>@Right(@Value(Range),4)Automatic) OR@Value(Year)In Range((@Evaluate(@Left(@Value(Range),4)),@Evaluate(@Right(@Value(Range),4)))NumericAll good features go through the passed port, regardless of type of range.
Hello,
First of all, thank you very much for your answers.
It works very good with the solution of markatsafe.
Good day
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
