Skip to main content
Question

Concatenate values in one column based on values in another

S
Hi,

 

I have an Excel spreadsheet similiar to Table 1 below

 

I would like to get a text file / Excel file with the Continent listed only once with the corresponding countries in a csv lists within one cell.

 

E.G.

 

 

Table1

 

Continent Country EUROPE Albania EUROPE Andorra EUROPE Armenia NORTH AMERICA Antigua and Barbuda NORTH AMERICA Bahamas NORTH AMERICA Barbados

 

 

Desired Result

 

Continent Country EUROPE Albania, Andorra, Armenia, ETC… NORTH AMERICA Antigua and Barbuda, Bahamas, Barbados, ETC…..

 

 

I 've been trying for ages and I can't compile a workspace to do it, any help very much appreciated :-)

 

 

6 replies

fmelizard
Safer
Forum|alt.badge.img+18
  • fmelizardSafer
  • Safer
  • June 6, 2013
Hi,

 

 

I would use the aggregator for this, in it you can specify attributes to concatenate (country) and group by (continent).

 

Hope this helps.
david_r
Celebrity
  • david_rCelebrity
  • June 6, 2013
Hi,

 

 

Itay is spot on. Consider also using the AttributeAccumulator if you don't need any geometries in your output features, it is quicker.

 

 

After the Aggregator / AttributAccumulator, use a ListConcatenator to create the contents for the Country" column based on the resulting list.

 

 

David
takashi
Influencer
  • takashiInfluencer
  • June 6, 2013
Hi,

 

 

I would follow Itay's suggestion. The Aggregator can output concatenated strings. In addition, if you need to use comma + white space as the separator between country names, you can specify ",<space>" for the "Separator Character" parameter of the Aggregator.

 

Takashi
ebygomm
Influencer
Forum|alt.badge.img+38
  • ebygommInfluencer
  • Influencer
  • June 6, 2013
Just to be different I would use a ListBuilder (grouping by Continent) followed by the ListConcatenator
S
  • shannon
  • Author
  • June 10, 2013
Hi guys,

 

Thank you for all the replies.

 

I was missing the Aggregator so that is where I fell down.

 

I'm using the aggreagator followed by the list concatenator and when I view the data through the visualiser all the continents are there with the associated countries but when I write it to excel I am not getting all the continents across?

 

Any idea why this is happening? I thought it might be the cell size in Excel for the concetenated list of countires so I increased this but it hasn't made any difference.

 

Thanks so much for your help.

 

Shannon
takashi
Influencer
  • takashiInfluencer
  • June 10, 2013
Hi Shannon,

 

 

if you had received some warning messages on the Log window when you wrote the Excel, would you paste those here to clarify the problem? And, if you specify "Attribute to Concatenate" parameter of the Aggregator, I think you may leave out the ListConcatenator.

 

 

Takashi

Reply


Helpful Members This Week

Community Stats

31,341
Posts
118,864
Replies
38,931
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.