Skip to main content
Question

Filter date from textfield with variables


mvloenhout
Participant
Forum|alt.badge.img

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.

10 replies

takashi
Evangelist
  • June 21, 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.


mvloenhout
Participant
Forum|alt.badge.img
  • Author
  • Participant
  • June 25, 2018

@takashiI will try your possible solution Thank you.


mvloenhout
Participant
Forum|alt.badge.img
  • Author
  • Participant
  • June 28, 2018

@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
Evangelist
  • June 28, 2018
mvloenhout wrote:

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

 

 


mvloenhout
Participant
Forum|alt.badge.img
  • Author
  • Participant
  • June 28, 2018
takashi wrote:
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
Evangelist
  • June 29, 2018
mvloenhout wrote:

@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:03Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)
21-12-2017 10:26Object opgepakt door WSDOMMEL\lothw1 (Projectleider, Route Project)

mvloenhout
Participant
Forum|alt.badge.img
  • Author
  • Participant
  • July 5, 2018
takashi wrote:
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:03Object opgepakt door WSDOMMEL\kempt1 (Projectleider, Route Project)
21-12-2017 10:26Object 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
Evangelist
  • July 5, 2018
mvloenhout wrote:

@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:23Object opgepakt door WSDOMMEL\spita1 (HandhaverRoute Vergunning)
23-2-2017 13:51Object doorgezet door WSDOMMEL\holtw1 (LandmeterRoute Vergunning)
23-2-2017 12:36Object opgepakt door WSDOMMEL\holtw1 (LandmeterRoute Vergunning)
21-2-2017 16:45Object doorgezet door WSDOMMEL\spita1 (HandhaverRoute Vergunning)
21-2-2017 16:41Object opgepakt door WSDOMMEL\spita1 (HandhaverRoute Vergunning)
20-2-2017 13:12Nieuwe Objectregistratiedoor WSDOMMEL\niess1
-------------
12-5-2018 11:03Object opgepakt door WSDOMMEL\kempt1 (ProjectleiderRoute Project)
13-3-2018 11:23Object doorgezet door WSDOMMEL\holtw1 (LandmeterRoute Project)
14-4-2017 14:05Object opgepakt door WSDOMMEL\holtw1 (LandmeterRoute Project)
12-4-2017 11:05Object doorgezet door WSDOMMEL\kempt1 (ProjectleiderRoute Project)
12-4-2017 11:03Object opgepakt door WSDOMMEL\kempt1 (ProjectleiderRoute Project)
17-3-2017 8:37Nieuwe Objectregistratiedoor WSDOMMEL\kempt1
-------------
5-4-2018 11:42Object opgepakt door WSDOMMEL\manss1 (ProjectleiderRoute Project)
5-4-2018 11:41Object doorgezet door WSDOMMEL\jansm2 (LandmeterRoute Project)
5-4-2018 11:40Object opgepakt door WSDOMMEL\jansm2 (LandmeterRoute Project)
5-4-2018 11:41Object doorgezet door WSDOMMEL\lothw1 (ProjectleiderRoute Project)
21-12-2017 10:26Object opgepakt door WSDOMMEL\lothw1 (ProjectleiderRoute Project) 
5-11-2017 11:55Nieuwe Objectregistratiedoor 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
Evangelist
  • July 5, 2018
mvloenhout wrote:

@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
Evangelist
  • July 5, 2018
mvloenhout wrote:

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

 

 


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