Skip to main content
  • EN

I have a dataset in Oracle. This feature class has a text-field with the following information in:

3/7/2017 1:34 PM: Object picked up by User 2

2/27/2017 4:50 PM: Object closed by User 3

2/21/2017 4:49 PM: Object picked up by User 2

2/20/2017 2:39 PM: New object by User 1

Where date, time and User are variables.

So the next information is also in the attribute table, but at another object

But I can also have the next information in the attribute:

3/7/2018 1:34 PM: Object picked up by User 6

2/29/2018 4:50 PM: Object closed by User 4

1/27/2018 4:49 PM: Object picked up by User 9

1/18/2018 2:39 PM: New object by User 8

I would like to filter just the date where User 2/ 9 picked up the object for he first time.

In the case above 2/21/2017 and 1/27/2018.

Hi @mvloenhout, a possible way is:

  1. Extract date/time part and user number part from the "picked up" record with a StringSearcher.
  2. Select only the features of interested user number (2 and 9) with a Tester.
  3. Convert the date/time string to FME standard format with a DateTimeConverter.
  4. Sort the features by date/time (FME standard format) [Edited] ascending with a Sorter.
  5. Select first features for each user number with a DuplicateFilter.

StringSearcher Parameters Example: Modify the Contains Regular Expressions appropriately according to the actual input text structure specification.

DateTimeConverter Parameters Example: Modify the Input Format appropriately according to the actual input date/time format specification. See these links to learn more about FME date/time.

@takashiI will try your possible solution Thank you.

@takashi

I've tried your sollution, but I can't get it working correctly.

 

Below three values wich are in 1 Attribute per object:

7-3-2017 13:23: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

23-2-2017 13:51: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

23-2-2017 12:36: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

21-2-2017 16:45: Object doorgezet door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

21-2-2017 16:41: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\\niess1

-------------

12-5-2018 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

13-3-2018 11:23: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

14-4-2017 14:05: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

12-4-2017 11:05: Object doorgezet door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\\kempt1

-------------

5-4-2018 11:42: Object opgepakt door WSDOMMEL\\manss1 (Projectleider, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:40: Object opgepakt door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

21-12-2017 10:26: Object opgepakt door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\\lothw1

As you can see, there are many values variables, but also many values almost the same, and only the date/ time is different.

I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project) of by a "Handhaver" (Handhaver, Route Vergunning).

So in the examples above the next dates in bold:

 

21-2-2017, 12-4-2017, 21-12-2017.

Your solution I can't get it working.

The date formatter will work I guess, but my problem is in the StringSearcher.

 

Hopelfully you can help me.

Thank you very much!

 

Marlies

@takashi

I've tried your sollution, but I can't get it working correctly.

 

Below three values wich are in 1 Attribute per object:

7-3-2017 13:23: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

23-2-2017 13:51: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

23-2-2017 12:36: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

21-2-2017 16:45: Object doorgezet door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

21-2-2017 16:41: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\\niess1

-------------

12-5-2018 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

13-3-2018 11:23: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

14-4-2017 14:05: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

12-4-2017 11:05: Object doorgezet door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\\kempt1

-------------

5-4-2018 11:42: Object opgepakt door WSDOMMEL\\manss1 (Projectleider, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:40: Object opgepakt door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

21-12-2017 10:26: Object opgepakt door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\\lothw1

As you can see, there are many values variables, but also many values almost the same, and only the date/ time is different.

I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project) of by a "Handhaver" (Handhaver, Route Vergunning).

So in the examples above the next dates in bold:

 

21-2-2017, 12-4-2017, 21-12-2017.

Your solution I can't get it working.

The date formatter will work I guess, but my problem is in the StringSearcher.

 

Hopelfully you can help me.

Thank you very much!

 

Marlies

Since the format of text is completely different from the first example, naturally you have to modify the regex and date/time format appropriately.

 

I have a question. Why two dates 12-4-2017 and 21-12-2017 should be selected even though both are picked up by the same "(Projectleider, Route Project)"?

 

 

Since the format of text is completely different from the first example, naturally you have to modify the regex and date/time format appropriately.

 

I have a question. Why two dates 12-4-2017 and 21-12-2017 should be selected even though both are picked up by the same "(Projectleider, Route Project)"?

 

 

We want that date, because thats the date the Object really starts to "Live".

 

The first date is the moment of licensing, the second date (that we want to have), the moment the object is built in the field.

 

 

So every object/ workflow has another date we would like to return in a field.

 

 

@takashi

I've tried your sollution, but I can't get it working correctly.

 

Below three values wich are in 1 Attribute per object:

7-3-2017 13:23: Object opgepakt door WSDOMMEL\spita1 (Handhaver, Route Vergunning)

 

23-2-2017 13:51: Object doorgezet door WSDOMMEL\holtw1 (Landmeter, Route Vergunning)

 

23-2-2017 12:36: Object opgepakt door WSDOMMEL\holtw1 (Landmeter, Route Vergunning)

 

21-2-2017 16:45: Object doorgezet door WSDOMMEL\spita1 (Handhaver, Route Vergunning)

 

21-2-2017 16:41: Object opgepakt door WSDOMMEL\spita1 (Handhaver, Route Vergunning)

 

20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\niess1

-------------

12-5-2018 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)

 

13-3-2018 11:23: Object doorgezet door WSDOMMEL\holtw1 (Landmeter, Route Project)

 

14-4-2017 14:05: Object opgepakt door WSDOMMEL\holtw1 (Landmeter, Route Project)

 

12-4-2017 11:05: Object doorgezet door WSDOMMEL\kempt1 (Projectleider, Route Project)

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)

 

17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\kempt1

-------------

5-4-2018 11:42: Object opgepakt door WSDOMMEL\manss1 (Projectleider, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:40: Object opgepakt door WSDOMMEL\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\lothw1 (Projectleider, Route Project)

 

21-12-2017 10:26: Object opgepakt door WSDOMMEL\lothw1 (Projectleider, Route Project) 

 

5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\lothw1

As you can see, there are many values variables, but also many values almost the same, and only the date/ time is different.

I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project)  of by a "Handhaver" (Handhaver, Route Vergunning).

So in the examples above the next dates in bold:

 

21-2-2017, 12-4-2017, 21-12-2017.

Your solution I can't get it working.

The date formatter will work I guess, but my problem is in the StringSearcher.

 

Hopelfully you can help me.

Thank you very much!

 

Marlies

hmm, still unclear. How can you distinguish "the moment of licensing" from "the moment the object is built in the field", based on the text strings you have posted?

 

Could you please explain why these two dates should be selected for "Projectleider"?

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)
21-12-2017 10:26: Object opgepakt door WSDOMMEL\lothw1 (Projectleider, Route Project)
hmm, still unclear. How can you distinguish "the moment of licensing" from "the moment the object is built in the field", based on the text strings you have posted?

 

Could you please explain why these two dates should be selected for "Projectleider"?

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)
21-12-2017 10:26: Object opgepakt door WSDOMMEL\lothw1 (Projectleider, Route Project)
These are the dates of a Workflow. I will take the first Workflow, starts at 20-2-2017 and ends at 7-3-2018.

 

1) The first date (20-2-2017) is the date that a farmer asks a license to possibly build an object in the field.

 

2) The second date (21-2-2017) is the date the farmer realy build it.SpitA1 sees this and than startsthe counting of weeks of the workflow. 

 

Every step has a lead time of a couple of weeks rated by the organisation

 

 

What we really want is tot know how many time is spent from Stwp 2 till today.

 

 

I hope this is a little bit more clear for you.

 

@takashi

I've tried your sollution, but I can't get it working correctly.

 

Below three values wich are in 1 Attribute per object:

7-3-2017 13:23: Object opgepakt door WSDOMMEL\spita1 (Handhaver, Route Vergunning)

 

23-2-2017 13:51: Object doorgezet door WSDOMMEL\holtw1 (Landmeter, Route Vergunning)

 

23-2-2017 12:36: Object opgepakt door WSDOMMEL\holtw1 (Landmeter, Route Vergunning)

 

21-2-2017 16:45: Object doorgezet door WSDOMMEL\spita1 (Handhaver, Route Vergunning)

 

21-2-2017 16:41: Object opgepakt door WSDOMMEL\spita1 (Handhaver, Route Vergunning)

 

20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\niess1

-------------

12-5-2018 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)

 

13-3-2018 11:23: Object doorgezet door WSDOMMEL\holtw1 (Landmeter, Route Project)

 

14-4-2017 14:05: Object opgepakt door WSDOMMEL\holtw1 (Landmeter, Route Project)

 

12-4-2017 11:05: Object doorgezet door WSDOMMEL\kempt1 (Projectleider, Route Project)

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)

 

17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\kempt1

-------------

5-4-2018 11:42: Object opgepakt door WSDOMMEL\manss1 (Projectleider, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:40: Object opgepakt door WSDOMMEL\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\lothw1 (Projectleider, Route Project)

 

21-12-2017 10:26: Object opgepakt door WSDOMMEL\lothw1 (Projectleider, Route Project) 

 

5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\lothw1

As you can see, there are many values variables, but also many values almost the same, and only the date/ time is different.

I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project)  of by a "Handhaver" (Handhaver, Route Vergunning).

So in the examples above the next dates in bold:

 

21-2-2017, 12-4-2017, 21-12-2017.

Your solution I can't get it working.

The date formatter will work I guess, but my problem is in the StringSearcher.

 

Hopelfully you can help me.

Thank you very much!

 

Marlies

All we need is the rule for determining which date(s) should be selected from the given text.

 

7-3-2017 13:23: Object opgepakt door WSDOMMEL\spita1 (Handhaver, Route Vergunning)
23-2-2017 13:51: Object doorgezet door WSDOMMEL\holtw1 (Landmeter, Route Vergunning)
23-2-2017 12:36: Object opgepakt door WSDOMMEL\holtw1 (Landmeter, Route Vergunning)
21-2-2017 16:45: Object doorgezet door WSDOMMEL\spita1 (Handhaver, Route Vergunning)
21-2-2017 16:41: Object opgepakt door WSDOMMEL\spita1 (Handhaver, Route Vergunning)
20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\niess1
-------------
12-5-2018 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)
13-3-2018 11:23: Object doorgezet door WSDOMMEL\holtw1 (Landmeter, Route Project)
14-4-2017 14:05: Object opgepakt door WSDOMMEL\holtw1 (Landmeter, Route Project)
12-4-2017 11:05: Object doorgezet door WSDOMMEL\kempt1 (Projectleider, Route Project)
12-4-2017 11:03: Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)
17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\kempt1
-------------
5-4-2018 11:42: Object opgepakt door WSDOMMEL\manss1 (Projectleider, Route Project)
5-4-2018 11:41: Object doorgezet door WSDOMMEL\jansm2 (Landmeter, Route Project)
5-4-2018 11:40: Object opgepakt door WSDOMMEL\jansm2 (Landmeter, Route Project)
5-4-2018 11:41: Object doorgezet door WSDOMMEL\lothw1 (Projectleider, Route Project)
21-12-2017 10:26: Object opgepakt door WSDOMMEL\lothw1 (Projectleider, Route Project) 
5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\lothw1
So far, the rule you have explained is "I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project) of by a "Handhaver" (Handhaver, Route Vergunning)". That's it.

 

According to the known rule, we can understand why "21-2-2017" (first time of "Object opgepakt - Handhaver") and  "12-4-2017" (first time of "Object opgepakt - Projectleider") should be selected. However, we cannot understand why "21-12-2017" (second time of "Object opgepakt - Projectleider") should also be selected.

 

@takashi

I've tried your sollution, but I can't get it working correctly.

 

Below three values wich are in 1 Attribute per object:

7-3-2017 13:23: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

23-2-2017 13:51: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

23-2-2017 12:36: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

21-2-2017 16:45: Object doorgezet door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

21-2-2017 16:41: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\\niess1

-------------

12-5-2018 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

13-3-2018 11:23: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

14-4-2017 14:05: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

12-4-2017 11:05: Object doorgezet door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\\kempt1

-------------

5-4-2018 11:42: Object opgepakt door WSDOMMEL\\manss1 (Projectleider, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:40: Object opgepakt door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

21-12-2017 10:26: Object opgepakt door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\\lothw1

As you can see, there are many values variables, but also many values almost the same, and only the date/ time is different.

I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project) of by a "Handhaver" (Handhaver, Route Vergunning).

So in the examples above the next dates in bold:

 

21-2-2017, 12-4-2017, 21-12-2017.

Your solution I can't get it working.

The date formatter will work I guess, but my problem is in the StringSearcher.

 

Hopelfully you can help me.

Thank you very much!

 

Marlies

Ah, perhaps is the sequence of dates separated into three parts by "Nieuwe Objectregistratie" and do you need to apply the rule for each part separately?

 

I don't know what "Nieuwe Objectregistratie" means. I'm afraid that it's the reason why I cannot understand your explanation at all.

 

 

@takashi

I've tried your sollution, but I can't get it working correctly.

 

Below three values wich are in 1 Attribute per object:

7-3-2017 13:23: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

23-2-2017 13:51: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

23-2-2017 12:36: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Vergunning)

 

21-2-2017 16:45: Object doorgezet door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

21-2-2017 16:41: Object opgepakt door WSDOMMEL\\spita1 (Handhaver, Route Vergunning)

 

20-2-2017 13:12: Nieuwe Objectregistratie, door WSDOMMEL\\niess1

-------------

12-5-2018 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

13-3-2018 11:23: Object doorgezet door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

14-4-2017 14:05: Object opgepakt door WSDOMMEL\\holtw1 (Landmeter, Route Project)

 

12-4-2017 11:05: Object doorgezet door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

12-4-2017 11:03: Object opgepakt door WSDOMMEL\\kempt1 (Projectleider, Route Project)

 

17-3-2017 8:37: Nieuwe Objectregistratie, door WSDOMMEL\\kempt1

-------------

5-4-2018 11:42: Object opgepakt door WSDOMMEL\\manss1 (Projectleider, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:40: Object opgepakt door WSDOMMEL\\jansm2 (Landmeter, Route Project)

 

5-4-2018 11:41: Object doorgezet door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

21-12-2017 10:26: Object opgepakt door WSDOMMEL\\lothw1 (Projectleider, Route Project)

 

5-11-2017 11:55: Nieuwe Objectregistratie, door WSDOMMEL\\lothw1

As you can see, there are many values variables, but also many values almost the same, and only the date/ time is different.

I would like the date where the Object is picked up for the first time by a "Projectleider" (Projectleider, Route Project) of by a "Handhaver" (Handhaver, Route Vergunning).

So in the examples above the next dates in bold:

 

21-2-2017, 12-4-2017, 21-12-2017.

Your solution I can't get it working.

The date formatter will work I guess, but my problem is in the StringSearcher.

 

Hopelfully you can help me.

Thank you very much!

 

Marlies

If I understood the requirement correctly, this wokflow might help you.

 

 

Community Stats

31,951
Posts
121,336
Replies
39,562
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.