I want to combine the rows so that there are rows of three columns:
enter time
exit time
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:
Output:
How 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:
@masonwong19 Possibly aggregating (Aggregator) by License plate and the sorting (ListSorter) and processing by date the resulting list might give you what you need
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.
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
@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?
This is the output we are getting from the workflow:
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?
This is the output we are getting from the workflow:
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.
@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
@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.
@masonwong19 Great. Thanks for the example data. I think this will get you close. the key is the Adjacent Features in the AttributeCreator:
Adjacent 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.
@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:
@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!
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.