span8
span4
span8
span4
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:
Ref | Date |
1 | 01/01/2019 |
1 | 01/01/2019 |
2 | 01/01/2019 |
2 | 01/01/2019 |
2 | |
3 | 01/01/2019 |
3 | |
4 | 01/01/2019 |
4 | 01/01/2019 |
Output:
Ref | Date | NewField |
1 | 01/01/2019 |
Yes |
1 | 01/01/2019 |
Yes |
2 | 01/01/2019 |
No |
2 | 01/01/2019 |
No |
2 | No | |
3 | 01/01/2019 |
No |
3 | No | |
4 | 01/01/2019 |
Yes |
4 | 01/01/2019 |
Yes |
Thanks,
Sideshow
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) maxWHERE AA.Ref = max.Ref) ccLEFT OUTER JOIN (SELECT Ref FROM "Sheet1" WHERE DATE IS NULL) ddON 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
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
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.
Test/Filter values of related xml atributes0 Answers
How to extract numbers and characters from a string?6 Answers
Use internal FME attributes2 Answers
Convert Month text to number3 Answers
Deleting "unknown" attributes ?2 Answers
© 2019 亚搏在线Safe Software Inc |Legal