I'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.
How 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.
How 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