Solved

Merge Rows in FME

M
Badge

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?

icon

Best answer by markatsafe 23 January 2023, 19:31

View original
1 Attachment

12 replies

hkingsbury
Rank
Userlevel 5
Badge +29

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?

 

Check out the Abley FME Firesides on Youtube: https://www.youtube.com/@ableyltd
M
Badge +2

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

M
Badge

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
Rank
Userlevel 5
Badge +29

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
Check out the Abley FME Firesides on Youtube: https://www.youtube.com/@ableyltd
M
Badge

@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
Rank
Userlevel 4
Badge +25

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.

M
Badge +2

@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

M
Badge

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

1 Attachment
M
Badge +2

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

M
Badge

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

 

M
Badge +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.

 

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

imageRevised workspace attached.

M
Badge

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

Reply


Community Stats

28,661
Posts
109,324
Replies
36,528
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.