Skip to main content
  • EN

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?

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!

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

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

Here it is again, slightly changed, in 2015.

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

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


Community Stats

30,112
Posts
114,305
Replies
37,745
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.