Question

Testing for between dates supplied by external file

  • 16 March 2016
  • 5 replies
  • 18 views
ebygomm
Rank
Userlevel 1
Badge +10

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
Badge

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
Rank
Userlevel 1
Badge +10

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
Badge

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

Here it is again, slightly changed, in 2015.

K
Badge

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

takashi
Rank
Userlevel 2
Badge +17

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

28,669
Posts
109,361
Replies
36,531
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.