Skip to main content
Solved

Merge Rows in FME


Forum|alt.badge.img

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.

 

Attached is an image of the input data as well as an image of what we want to create on the output. The small dataset is also attached.

Input:

inputOutput:

outputHow can I achieve this with FME?

Best answer by markatsafe

masonwong19 wrote:

@Mark Stoakes​ Thanks, this is very helpful! However, I am still unsure on how to merge the exit times. I see that you 'drop the exit records', but instead how would you merge the exit record to the corresponding entry time?

 

Sorry for bothering you with all these questions, this is my first time using FME and I don't have an intuition for which transformers are needed to be used.

 

@masonwong19​  Sorry - my mistake. The toTime Conditional should look like this:

imageRevised workspace attached.

View original
Did this help you find an answer to your question?

12 replies

hkingsbury
Celebrity
Forum|alt.badge.img+53
  • Celebrity
  • January 19, 2023

Do you have a flag on the input data which is the entry and which is the exit time? Or do you also need to figure that out?

 


Forum|alt.badge.img+2
  • January 19, 2023

@masonwong19​ Possibly aggregating (Aggregator) by License plate and the sorting (ListSorter) and processing by date the resulting list might give you what you need


Forum|alt.badge.img
  • Author
  • January 19, 2023
hkingsbury wrote:

Do you have a flag on the input data which is the entry and which is the exit time? Or do you also need to figure that out?

 

we know which inputs are entry and exit times. We have two sources of data: one contains columns of license plate and entrance time, the other contains columns of license plate and exit times.


hkingsbury
Celebrity
Forum|alt.badge.img+53
  • Celebrity
  • January 19, 2023
masonwong19 wrote:

we know which inputs are entry and exit times. We have two sources of data: one contains columns of license plate and entrance time, the other contains columns of license plate and exit times.

Cool, basically what @Mark Stoakes​ said

  • rename the time fields to in/out respectively
  • use the aggregator (group by license plate) and merge attributes from all features

Forum|alt.badge.img
  • Author
  • January 20, 2023
markatsafe wrote:

@masonwong19​ Possibly aggregating (Aggregator) by License plate and the sorting (ListSorter) and processing by date the resulting list might give you what you need

We have tried what you and @hkingsbury​ have suggested, however the output is still not what we are looking for.

 

Here is what our workflow looks like as well as some details on our configuration. Is there anything that seems off in our configuration?

image (3)image (2)image (1)This is the output we are getting from the workflow:

image 

 


redgeographics
Celebrity
Forum|alt.badge.img+49
masonwong19 wrote:

We have tried what you and @hkingsbury​ have suggested, however the output is still not what we are looking for.

 

Here is what our workflow looks like as well as some details on our configuration. Is there anything that seems off in our configuration?

image (3)image (2)image (1)This is the output we are getting from the workflow:

image 

 

The Aggregator is creating a list (a 1-to-many relationship on a single feature). In other words, if one vehicle enters and exits twice within the period you're looking at it'll have 2 entries in that list.

 

Using a ListExploder after your ListSorter should do the trick.


Forum|alt.badge.img+2
  • January 20, 2023

@masonwong19​ A picture is worth a thousand words. A small sample dataset is worth a thousand pictures. If you can attach a small example sample dataset one of might be able to show you a possible solution


Forum|alt.badge.img
  • Author
  • January 20, 2023
markatsafe wrote:

@masonwong19​ A picture is worth a thousand words. A small sample dataset is worth a thousand pictures. If you can attach a small example sample dataset one of might be able to show you a possible solution

@Mark Stoakes​ I have attached images of the expected input and the output we would like to generate. Please note that the input can have duplicate license plates.

I have attached a small set of example data as well.

Input: inputOutput:

outputAny advice would be greatly appreciated 🙂


Forum|alt.badge.img+2
  • January 20, 2023

@masonwong19​ Great. Thanks for the example data. I think this will get you close. the key is the Adjacent Features in the AttributeCreator:

imageAdjacent Features let's us use an attribute in the next (or previous) feature: feature[+1].type

I've attached the example workspace (FME 2022.2)


Forum|alt.badge.img
  • Author
  • January 21, 2023
markatsafe wrote:

@masonwong19​ Great. Thanks for the example data. I think this will get you close. the key is the Adjacent Features in the AttributeCreator:

imageAdjacent Features let's us use an attribute in the next (or previous) feature: feature[+1].type

I've attached the example workspace (FME 2022.2)

@Mark Stoakes​ Thanks, this is very helpful! However, I am still unsure on how to merge the exit times. I see that you 'drop the exit records', but instead how would you merge the exit record to the corresponding entry time?

 

Sorry for bothering you with all these questions, this is my first time using FME and I don't have an intuition for which transformers are needed to be used.

 


Forum|alt.badge.img+2
  • Best Answer
  • January 23, 2023
masonwong19 wrote:

@Mark Stoakes​ Thanks, this is very helpful! However, I am still unsure on how to merge the exit times. I see that you 'drop the exit records', but instead how would you merge the exit record to the corresponding entry time?

 

Sorry for bothering you with all these questions, this is my first time using FME and I don't have an intuition for which transformers are needed to be used.

 

@masonwong19​  Sorry - my mistake. The toTime Conditional should look like this:

imageRevised workspace attached.


Forum|alt.badge.img
  • Author
  • January 25, 2023
markatsafe wrote:

@masonwong19​ A picture is worth a thousand words. A small sample dataset is worth a thousand pictures. If you can attach a small example sample dataset one of might be able to show you a possible solution

Sorry for the late response @Mark Stoakes​. The solution you provided fit our needs. Thank you so much for all your help, we really appreciate it!


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings