Question

Help with InlineQuerier

  • 10 February 2019
  • 9 replies
  • 24 views
mmccart
Rank
Badge +13

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

9 replies

M
Badge +2

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

takashi
Rank
Userlevel 2
Badge +17

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.

ebygomm
Rank
Userlevel 1
Badge +21

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
mmccart
Rank
Badge +13

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.

 

 

mmccart
Rank
Badge +13

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.

M
Badge +2

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.

takashi
Rank
Userlevel 2
Badge +17

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.

ebygomm
Rank
Userlevel 1
Badge +21

@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.

mmccart
Rank
Badge +13

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

Reply


Community Stats

28,731
Posts
109,527
Replies
36,565
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.