Additionally, I would then like to create another new field, that details the latest date of each reference number that returned "No" previously.
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.
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
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
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
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
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?
What are you merging on in that feature merger?
Using Ref as both Requestor and Supplier
