• EN
Question

Merging multiple rows to one row

  • 15 April 2022
  • 4 replies
  • 272 views
lorinda
Rank
Badge +5

Hi All,

I'm attempting to take a table that has 600K+ rows that have an individual value for a type of utility per location in it, join/merge the up to 8 values into one row to be able to join to another feature class at a later date based on the location unique id. I've tried aggregator, feature merger and feature joiner. Nothing is giving me the desired results. I've used the aggregator to get the unique location id, then used feature merger or joiner to attach the additional columns to the one location id. Tried to create the list, still not getting desired results. I get only one filled in. I've used testers to put yes in the named utility column based on the code type, and then where the values remained missing/null, I used the NullAttributeMapper to change them all to N in order to concatenate the data and get all columns accounted for, still missing some of the values.

It's likely something that I'm overlooking, but have been working on it for two or three days now and I'm just not seeing it. Any help would be appreciated.

Example:

Location 1 is listed 3 times in the table with util 1, util 2 and util 3, other 5 utilities are not represented.

Desired results:

Location 1, Y, N, Y, Y, N, N,N,N

Thanks,

Lorinda

4 replies

nielsgerrits
Rank
Userlevel 6
Badge +33

This should not be hard. Can you add a sample of your source data?

lorinda
Rank
Badge +5

You're right, it shouldn't be this hard. I've already done the process in the past in Access, trying to move it over to FME processing from SQL Server so that we don't have the 2gb limitation as the rest of the data with this one table make up about 3 gb or more of data.

I've tried it without changing the nulls/missing to N and with it. Nothing is appending the data together that I've tried.

This is a small sample. The data is actually in a sql database. There are 656K+ rows that when aggregated leaves 338K+ rows.

Thanks,

Lorinda

 

 

1 Attachment
M
Badge +2

@lorinda​ I think Aggregator with a Group By on the Location should work for you.

  • Mode: Attributes Only
  • Attribute Accumulation - Accumulation Mode: Merge Incoming Attributes

image

lorinda
Rank
Badge +5

Hi Mark,

Yes, I did use that situation, but I had included the concatenation with commas and it was royally messing up. I attempted it earlier today without the concatenation but with the merge attributes and merge incoming using a group by with the location, I also removed the null attribute mapper to leave the ones missing that were missing. It worked, however, it did concatenate the location id for however many times it was in the data (up to eight times). So the simple solution to that one was to do a left string for the 10 characters of the location id. Just had to get away from it for a while to rethink the logic. Now I'm on to adding even more data to the mix to get better results. The data is coming from about 4 tables that are all loosely tied together.

Thanks,

Lorinda

Reply


Community Stats

28,807
Posts
109,804
Replies
36,622
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.