• Home
  • Forums
  • FME Form
  • Authoring
  • Lookup a specific re-ocurring attribute and return Yes/no depending if all occurences of the attribute possess a populated date column
Question

Lookup a specific re-ocurring attribute and return Yes/no depending if all occurences of the attribute possess a populated date column

  • 21 February 2019
  • 7 replies
  • 0 views
S

I have tried and failed and tried and failed. Any help is extremely appreciated in advance!

 

I have column1 containing reference numbers (with duplicates) and I have column2 containing dates (and blanks). I want to create column3 and return yes/no values for all the reference numbers, depending if all ooccurrences of each reference number contains a populated date field. e.g:

input:

RefDate101/01/2019101/01/2019

 

201/01/2019

 

201/01/2019

 

2

 

301/01/2019

 

3

 

401/01/2019

 

401/01/2019

 

 

Output:

RefDateNewField101/01/2019

 

Yes101/01/2019

 

Yes201/01/2019

 

No201/01/2019

 

No2

 

No301/01/2019

 

No3

 

No401/01/2019

 

Yes401/01/2019

 

Yes

 

Thanks,

Sideshow

 

7 replies

S

Additionally, I would then like to create another new field, that details the latest date of each reference number that returned "No" previously.

jdh
Rank
Badge +22

Aggregate (with List) group by Ref, ListSearcher on _list{}.Date for the nulldate value (null, empty string), if found AttributeCreator Newfield = No, ListSorter (Date, descending order), ListIndexer (0, prefix latest_), if not found AttributeCreator Newfiled = Yes.

 

Both streams should then go to a deaggregator.
ebygomm
Rank
Userlevel 1
Badge +10

How about some sql in the InlineQuerier?

Select *, CASE WHEN dd.Ref IS NULL THEN "Yes" ELSE "No" END AS NEWFIELD from
(SELECT AA.Ref, AA.Date, Max.Latest_Date from Sheet1 aa,
(SELECT Ref, MAX(DATE) AS Latest_Date FROM "Sheet1" GROUP BY Ref) max
WHERE AA.Ref = max.Ref) cc
LEFT OUTER JOIN (SELECT Ref FROM "Sheet1" WHERE DATE IS NULL) dd
ON cc.Ref = dd.Ref

In reality I'd probably use this sort of workflow

0684Q00000ArLQXQA3.png

S

How about some sql in the InlineQuerier?

Select *, CASE WHEN dd.Ref IS NULL THEN "Yes" ELSE "No" END AS NEWFIELD from
(SELECT AA.Ref, AA.Date, Max.Latest_Date from Sheet1 aa,
(SELECT Ref, MAX(DATE) AS Latest_Date FROM "Sheet1" GROUP BY Ref) max
WHERE AA.Ref = max.Ref) cc
LEFT OUTER JOIN (SELECT Ref FROM "Sheet1" WHERE DATE IS NULL) dd
ON cc.Ref = dd.Ref

In reality I'd probably use this sort of workflow

0684Q00000ArLQXQA3.png

Hi Egomm, many thanks for the response.

 

Forgive me if the answer is obvious, but what exactly is going on with the tester and featuremerger in your screenshot? I have managed to use the statisticscalculator successfully to calculate the max date, but cannot seem to create a yes/no value based on whether all occurrences of the reference number possess a date.

 

Many thanks,

Sideshow

ebygomm
Rank
Userlevel 1
Badge +10

Hi Egomm, many thanks for the response.

 

Forgive me if the answer is obvious, but what exactly is going on with the tester and featuremerger in your screenshot? I have managed to use the statisticscalculator successfully to calculate the max date, but cannot seem to create a yes/no value based on whether all occurrences of the reference number possess a date.

 

Many thanks,

Sideshow

The tester filters out all the references with null values which then exit the failed port. These are then used as suppliers in the featureMerger. This way, only refs that have null values exit the merged port, everything else exits the unmerged requestor port

S

The tester filters out all the references with null values which then exit the failed port. These are then used as suppliers in the featureMerger. This way, only refs that have null values exit the merged port, everything else exits the unmerged requestor port

What are you merging on in that feature merger?

ebygomm
Rank
Userlevel 1
Badge +10

What are you merging on in that feature merger?

Using Ref as both Requestor and Supplier

Reply


Community Stats

28,657
Posts
109,304
Replies
36,529
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.