Skip to main content
Solved

Do I need a list of a list attribute? Regex to extrapolate to a list attribute?

  • September 27, 2018
  • 4 replies
  • 12 views
bwasserstein
Participant
Forum|alt.badge.img

I'm trying to get to the bottom of this non-spatial data issue and am racking my brain to no avail...

I have a table of data that looks as follows...

IDFacilityCode1A001,D0471D047,P001,G00512D001,D047,F0012F001,G0092P001,A0013G0053G0054A001

Notice that the FacilityCode may be duplicated between records for any particular ID. I need my final table to just be a DISTINCT list of IDs with an alphabetized list of unique FacilityCodes, so it should look like this...

IDFacilityCode1A001,D047,P001,G0052A001,D001,D047,F001,G009,P0013G0054A001

Theoretically, I'm thinking I might be able to use regex to extrapolate the unique FacilityCodes including the comma-separated values for the records with multiple FacilityCodes (with a StringSearcher? Not even sure what my regex would look like), then a ListDuplicateRemover, a ListSorter (to alphabetize), then a ListConcatenator to bring my list back to a single attribute.

I'm not even certain that could all work correctly while maintaining a grouping on the ID. I thought about using a SELECT GROUP_CONCAT(FacilityCode,",")) sql statement with an inlinequerier, then going from there...but I'm not certain how well that would work either.

Thanks in advance for any help! I'll keep this post updated if I find any solutions on my own. I'm running a 2015 version of FME via ArcGIS 10.3 data interop.

Best answer by hollyatsafe

Hi @bwasserstein,

Please have a look at this image to build your workflow (I'm using 2018 so can't send over a template but believe this should still be doable in 2015):

First I used an Aggregator to combine each ID, then I used an Attribute Splitter to generate a list feature and remove attributes with the ListDuplicateRemover, before joining the list back together with the Concatenator.

Workspace:

Output:

(You can just add a sorter to the end to reorder these!)

View original
Did this help you find an answer to your question?

4 replies

H
Forum|alt.badge.img+2
  • hollyatsafe
  • Best Answer
  • September 27, 2018

Hi @bwasserstein,

Please have a look at this image to build your workflow (I'm using 2018 so can't send over a template but believe this should still be doable in 2015):

First I used an Aggregator to combine each ID, then I used an Attribute Splitter to generate a list feature and remove attributes with the ListDuplicateRemover, before joining the list back together with the Concatenator.

Workspace:

Output:

(You can just add a sorter to the end to reorder these!)

bwasserstein
Participant
Forum|alt.badge.img
  • bwassersteinParticipant
  • Author
  • Participant
  • September 27, 2018
hollyatsafe wrote:

Hi @bwasserstein,

Please have a look at this image to build your workflow (I'm using 2018 so can't send over a template but believe this should still be doable in 2015):

First I used an Aggregator to combine each ID, then I used an Attribute Splitter to generate a list feature and remove attributes with the ListDuplicateRemover, before joining the list back together with the Concatenator.

Workspace:

Output:

(You can just add a sorter to the end to reorder these!)

Beautiful! Thank you, Holly...that works just fine in 2015 & was an easy solution I should have thought of. Somehow I ended up with leading commas for some of my "Code" concatenations, but a testfilter (@Value(Code) BEGINS_WITH ,) leading into a substring extractor took care of that. Fully handled with FME transformers!

 

 

I'm very grateful for you all at Safe & this forum :) Thanks again.

 

 

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • September 28, 2018
bwasserstein wrote:
Beautiful! Thank you, Holly...that works just fine in 2015 & was an easy solution I should have thought of. Somehow I ended up with leading commas for some of my "Code" concatenations, but a testfilter (@Value(Code) BEGINS_WITH ,) leading into a substring extractor took care of that. Fully handled with FME transformers!

 

 

I'm very grateful for you all at Safe & this forum :) Thanks again.

 

 

Try setting 'Yes' to the Drop Empty Part parameter in the AttributeSplitter (or the Drop Empty or Null Elements in the ListConcatenator) in order to remove the excess commas.
bwasserstein
Participant
Forum|alt.badge.img
  • bwassersteinParticipant
  • Author
  • Participant
  • September 28, 2018
takashi wrote:
Try setting 'Yes' to the Drop Empty Part parameter in the AttributeSplitter (or the Drop Empty or Null Elements in the ListConcatenator) in order to remove the excess commas.
That did the trick as well. Thanks @takashi.

Reply


Helpful Members This Week

Community Stats

30,908
Posts
117,105
Replies
38,708
Members

Latest FME

Terms & ConditionsCookie settings

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