Skip to main content
  • EN
I need to merge two files (excel).

 

I want to retain the common fields, but I want to remove duplicates, but also flag the data sources.

 

 

So for Example I have:

 

 

Readers

 

File A

 

Name, Postcode, DOB

 

Bob, NG1 1AA, 01/01/1970

 

 

File B

 

Name, Postcode, DOB

 

John, SW1 1AA, 01/01/1980

 

 

Writers

 

File C

 

Name, Postcode, DOB, FileAFlag, FileBFlag

 

Bob, NG1 1AA, 01/01/1970, Yes, No

 

John, SW1 1AA, 01/01/1980, No, Yes

 

 

I can merge the File A and B , simply by using 2 AttributeCreators and 1 AttributeKeeper and a Writer. 

 

 

------------------------------------------

 

 

The problem starts where I have duplicate entries and want to merge the data. See below:

 

 

Readers

 

File A

 

Name, Postcode, DOB

 

Bob, NG1 1AA, 01/01/1970

 

 

File B

 

Name, Postcode, DOB

 

Bob, NG1 1AA, 01/01/1970

 

 

Writers

 

File C

 

Name, Postcode, DOB, FileAFlag, FileBFlag

 

Bob, NG1 1AA, 01/01/1970, Yes, Yes

 

 

-------------------------------------------

 

 

Anyone know which transformers I need to use for this situation?

 

 

Thanks, S.

 

 

 

 

 

 

 

 

I would expose the fme_basename on the readers.

 

 

Use a matcher to route all unmatched features to the writer

 

Route the matched features to a duplicate remover and then change the fme_basename attribute to read A&B before connecting to the writer.
Hi EGomm, thanks for the feedback.

 

 

Not sure I follow the bit where you say I need to change the fme_basename attribute to read A&B before connecting to the writer.

 

 

 

Instead of having an attribute named FileAFlag and another named FileBFlag I would have one attribute of fme_basename, this would be set to the filename of the file the feature had come from.

 

 

So if File A is

 

Name Postcode DOB Bob NG1 1AA 01/01/1970 Sue NG15 7SG 01/05/1980

 

 

and File B is

 

 

Name Postcode DOB Bob NG1 1AA 01/01/1970 John SW1 1aa 01/01/1980

 

 

the features being written would be

 

 

Name, Postcode, DOB, fme_basename

 

Sue, NG15 7SG, 01/05/1980, File A

 

John, SW1 1AA, 01/01/1980, File B

 

Bob, NG1 1AA, 01/01/1970, File A & B

 

 

If you wanted to keep the FileAFlag, FileBFlag structure you can use a featuremerger after your attributecreators and connect the Merged, Not Merged & Unreferenced ports to the writer.
That is Magic. Looks to have done the trick.

 

 

I will test it out with a full set of data tomorrow and see if it works. Cheers, S.

Reply


Community Stats

30,351
Posts
115,341
Replies
38,081
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.