Skip to main content
Question

Testing for between dates supplied by external file

ebygomm
Influencer
Forum|alt.badge.img+39

I want to assign a value to an attribute based on a date range another attribute falls within. I can do this a number of ways, either with an attributerangemapper, or using the attributecreator with conditional formatting.

Both methods however require manually updating the transformer. Ideally I'd like to be able to use an external excel spreadsheet to define attributes and date ranges.

I can acheive this by using the spreadsheet to spatially create a timeline, use the created on time to create a point along this timeline and working out the spatial relationship, but it struck me that there should be a better way.

Any ideas?

5 replies

K
Forum|alt.badge.img+5
  • kim
  • March 17, 2016

Interesting question! I've played around a little bit on this end and I think I have two possible options that do what you are trying to achieve. They are included as an example attachment.

Both options assume that date ranges are non-overlapping, the ranges do not have to be contiguous, the end date in each range is up to (not up to and including) and the dates are in sortable number format. If not, you could always convert them using the DateFormatter.

Let me know if it works!

ebygomm
Influencer
Forum|alt.badge.img+39
  • ebygommInfluencer
  • Author
  • Influencer
  • March 17, 2016
kim wrote:

Interesting question! I've played around a little bit on this end and I think I have two possible options that do what you are trying to achieve. They are included as an example attachment.

Both options assume that date ranges are non-overlapping, the ranges do not have to be contiguous, the end date in each range is up to (not up to and including) and the dates are in sortable number format. If not, you could always convert them using the DateFormatter.

Let me know if it works!

Unfortunately I can't view this as I only have FME 2015

K
Forum|alt.badge.img+5
  • kim
  • March 17, 2016
ebygomm wrote:

Unfortunately I can't view this as I only have FME 2015

Here it is again, slightly changed, in 2015.

K
Forum|alt.badge.img+5
  • kim
  • March 17, 2016

Seems I can't put an attachment with a comment, so here it is in another answer, in 2015.

takashi
Evangelist
  • takashiEvangelist
  • March 17, 2016

Alternatively the InlineQuerier might also be a solution.
Assuming that each source feature has a date value with yyyymmdd format, and the Excel lookup table contains "begin" date, "end" date and "value". Note that the InlineQuerier (i.e. SQLite) doesn't support date/datetime data type. In this example, I'm using integer type to compare the date values.

0684Q00000ArKoSQAV.png

SQL Query Example:

select a.*, b.value from source as a left outer join lookup as b
on a.date between b.begin and b.end

Reply


Helpful Members This Week

Community Stats

31,393
Posts
119,068
Replies
39,018
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.