Question

Filter records based on dynamic values


I have a set of records (column-aligned text) that shows the entry and exit of cars in a particular building, where I have to group the records into specific groups based on the time field (Hour:Minute:Second) per hour (i.e. 09:00:00 - 10:00:00). This is to allow me to determine the occupancy rate per hour. My time field is based on the FME time format (HHMMSS). I have tried to use the tester transformer to try and group them by hour, but I realized that for every 1 hour the car may exit before the designated exit time, but it is still classified as "occupying" the building. Hence I am attempting to refine my query by grouping the cars per minute, summing up the total number of cars within 60min, and averaging out the total number of cars occupying within the 1hour period.

 

Due to the huge number of records, it is unfeasible for me to create queries for each minute (up to 24 hours) Hence, I would like to know if it is possible to create a dynamic query that automatically increases the query expression by 1 minute each time the records run through the tester transformer via a loop/or any other transformers up to 1hour (i.e. 09:00:00 - 10:00:00 -> 09:01:00 - 10:00:00), without typing in python as I have no prior experience in python.

4 replies

Userlevel 4

Hi

I'm note quite sure I understand the rules for how you calculate the occupancy, but you could consider using the ceil() and floor() math functions in a couple of ExpressionEvaluators to round the arrival and departure times to the nearest hour:

arrival = @floor(@Value(arrival)/10000.0)*10000
departure = @ceil(@Value(departure)/10000.0)*10000

If a car arrives e.g. at 092312 and leaves at 101245, this will set the arrival time to 090000 and the departure time to 110000, which should make it easy to assign it to defined, hourly time slots.

David

Userlevel 2
Badge +17

I don't really understand the requirement. For example, in this case:

  • car1 entry 09:10:00 - exit 09:40:00
  • car2 entry 09:40:00 - exit 10:40:00
  • car3 entry 10:20:00 - exit 12:10:00

the rules are as the followings?

  • the car1 occupies 30 minutes between 09-10 o'clock,
  • the car2 occupies 20 minutes between 09-10, and 40 minutes between 10-11
  • the car3 occupies 40 minutes between 10-11, 60 minutes between 11-12, and 10 minutes between 12-13.

Hi,

I'm trying to filter specific records within by each 1 hour segment (i.e. 090000 to 100000), but I was wondering if it is possible to create a form of dynamic query that increases the query expression by 1 minute for each loop that the query process undergoes so that I can ensure that the records retrieved fulfill the 1hour segment condition. (i.e. the query expression changes from 090000 to 090100 for the 1st loop, then it changes from 090100 t o 090200 for the 2nd loop)

Badge +3

Yes you can loop it with an index and create an expression using this index.

Though i probably would convert the time to numeric time.

Reply