+1I'm working with two sets of data with nearly identical, but not quite matching, IDs. Ideally I'd like to join together rows where:
|requester_id - supplier_id| < 0.1
Is there any way within FME to accomplish this? Only way I can think of is to do a cartesian join and filter the results, but for this data the cartesian join would result in billions of rows and I'd like to avoid that.
Best answer by takashi
The InlineQuerier might help you.
SQL Query Example:
select r.*, s.*
from requestor as r
inner join supplier as s
on abs(r.requestor_id - s.supplier_id) < 0.1
+22How about: Multiply both Id's by 10 and round them.
Store those multiplied ID's in a new attribute.
Then do the join on the new attribute and test on the above clause, using the original ID's.
+1How about: Multiply both Id's by 10 and round them.
Store those multiplied ID's in a new attribute.
Then do the join on the new attribute and test on the above clause, using the original ID's.
Nope, doesn't work for what I'm looking for. Let's say:
requester_id = 10.51
supplier_id = 10.60
They should match but with that method they'd resolve to 105 and 106. I've tried messing around with rounding the values and I don't think it's something that can work. Let's say
requester_id1 = 10.41
requester_id2 = 10.59
supplier_id = 10.50
I'd want both requesters to match the supplier but I can't see how I'd make that happen through rounding.
The InlineQuerier might help you.
SQL Query Example:
select r.*, s.*
from requestor as r
inner join supplier as s
on abs(r.requestor_id - s.supplier_id) < 0.1