Skip to main content
  • EN

Hi I'm currently trying to combine 2 datasets based on a shared set value where table 1 attribute 1 ex. = 500, and table 2 Attribute1 also = 500, and there would be duplicate 500 values in both datasets

so to do an effective Join I'm attempting to also join on *closest mileage value, the mileage is not exactly the same between the two tables so it would need to be within rage or closet to sort of function where first the join would be attribute 1 being the same between both tables and then joining table 2 to table 1 where the mileage from table 2 is cloest to table 1 mileage.

How big is your dataset? One fairly inefficient way is to merge on Attribute1 but allow duplicate suppliers and build a list. Then explode the list and test for the abs difference between the mileage values and sort by this difference followed by a duplicate filter to keep only the match with the smallest difference.

 

@richard_che​  Perhaps try AttributeRangeMapper to create discrete values for your closest mileage and add that as a key to your FeatureMerger, or you could try NeighborFinder and Group By Attribute1

Hi @ebygomm​ , thanks for your sharing your thoughts. I am new to FME and I am in a similar scenario as OP. It would be helpful if you can explain your answer in a bit more detail as I am unsure of how to implement it.

 

Here is a bit more detail about my problem:

I have rows of data with columns

  1. enter/exit time and
  2. license plate

I want to combine the rows so that there are rows of three columns:

  1. enter time
  2. exit time
  3. license plate

For each license plate and enter time, I want it to choose the closest exit time which comes after the enter time.

 

Below is the image of the feature merger settings as well as the output from the feature merger to an excel sheet.

image2image1

Reply


Community Stats

30,163
Posts
114,500
Replies
37,814
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.