Skip to main content
  • EN

I need help joining a table based on whether or not a feature falls within a date-time range.

Table A: Has an attribute with unique recorded date-times for all 3,233 records

Table B: Has a "Start Date-Time" and an "End Date-Time" field for all 29 records

I built this query:

and it was able to join and return 2,731 records that met the criteria.

Here's a sample of what was joined:

 

My issue now is how to join return the other 502 records that may not fall the range listed above. I've tried several iterations such as NOT BETWEEN, IS NO, <>, etc.

Any advice would be appreciated! Thanks.

 

Note: Tables are generated from a CSV so that's why I chose InlineQuerier.

FME 2018.1.1.2 (Build 18586) 64-bit

Isn’t it a different type of join you’re looking for? Possibly a right join maybe.

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join

Isn’t it a different type of join you’re looking for? Possibly a right join maybe.

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join

Agree. This type of join might be what you need.

Are you looking for all records returned, including unmerged in one query, or a separate query to return only unmerged?

Something like this for all records

SELECT * from mergedRecords M
left join
aRecords A
on M.fullDateG between A.startFullDateA and A.endFullDateA

or this if you just want to return unmerged

SELECT * from mergedRecords M, aRecords A
on M.fullDateG not between A.startFullDateA and A.endFullDateA

Isn’t it a different type of join you’re looking for? Possibly a right join maybe.

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join

Unfortunately, I get the following error that states RIGHT and FULL OUTER JOIN are not currently supported. I would love to use a a RIGHT JOIN here.

 

 

Are you looking for all records returned, including unmerged in one query, or a separate query to return only unmerged?

Something like this for all records

SELECT * from mergedRecords M
left join
aRecords A
on M.fullDateG between A.startFullDateA and A.endFullDateA

or this if you just want to return unmerged

SELECT * from mergedRecords M, aRecords A
on M.fullDateG not between A.startFullDateA and A.endFullDateA

@egomm the top script returns 3,236 records which is 3 more than 3,233 I originally started with. I ran a duplicate filter and inspected the 3 records and it is data I need to keep. I'll take this flow for now and swap out those 3 records some how. But it would be nice to avoid all of the extra transformers. Note: I tried SELECT DISTINCT and just SELECT and both statements returned the 3 extra records.

Unfortunately, I get the following error that states RIGHT and FULL OUTER JOIN are not currently supported. I would love to use a a RIGHT JOIN here.

 

 

That’s interesting as only ever used inner joins.

Unfortunately, I get the following error that states RIGHT and FULL OUTER JOIN are not currently supported. I would love to use a a RIGHT JOIN here.

 

 

"left join" is applicable.

@egomm the top script returns 3,236 records which is 3 more than 3,233 I originally started with. I ran a duplicate filter and inspected the 3 records and it is data I need to keep. I'll take this flow for now and swap out those 3 records some how. But it would be nice to avoid all of the extra transformers. Note: I tried SELECT DISTINCT and just SELECT and both statements returned the 3 extra records.

Do you have overlapping periods in the table you are matching to? This would result in additional values as all matches in the left table are returned.

Ended up leveraged the 'Adjacent Feature Attributes' function to back-fill missing data fields.

Reply


Community Stats

31,801
Posts
120,810
Replies
39,422
Members

Latest FME

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.