Question

how to compare two excel lists for matched and non matched items when comparing each list to one another

  • 7 March 2014
  • 5 replies
  • 35 views
J
I just started using FME a few days ago and after extensive/unsuccessful search on FME website I have to ask for help:

 

 

I have two lists (each an excel file) and I want to compare each item in the list of one file to each item in the list of the other excel file.

 

 

The lists could be alphabetic or numeric or a combination of both (normally arranged in a column). Example: list#1 = john, ralph, street, egg, computer, mouse. List#2 = Mike, Nike, keyboard, 123, run, letters, words, eye, fingers, help, egg

 

My desired result should be "unique items" = john, ralph, street, computer, mouse, Mike, Nike, keyboard, 123, run, letters, words, eye, fingers, help. And "Matched items" = egg

 

 

Help is greatly appreciated it. As detailed as possible please since I am just starting using FME. In fact posting the workspace for me to examine and learn from would be ideal. Thanks

5 replies

jon
Rank
Badge +5
Have you tried using the ChangeDetector transformer?  I borrowed upon this post to compare two geodatabase schemas:

 

 

https://safecommunity.force.com/CommunityAnswers?id=906a0000000csZoAAI

 

 

 

Here's what my workbench ended up looking like - I'm sure you can do the same using Excel

 

 

J
I actually tried something very similar before but it did not worked. The above example did not worked until I actually put my data horizontally (in a line from left to right) in excel as opposed to the vertical format I had. Thanks for the help, this is a great start. I can at least get some useful results that I can further filter and play with
takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

If the list is stored in a cell as csv (comma separated values), the AttributeSplitter can be used to split it at comma and store every element in a list attribute (e.g. _list{}).

 

You can then apply Jon's suggestion. i.e. the ListExploder and the ChangeDetector.

 

 

Takashi
G
Badge +3
This is again a standard cartesian product full comparison.

 

U can either use Takashi suggestion to make a list of it. (if u dan't want to export to csv but use your excell, u can add a commonattriubte (i usually make an attriubte named somehintg like

 

attributgename= 'lister", value =1 and build the list on that listbuilder group by "lister").

 

Then proceed with Jon's suggestion using the changedetector.

 

 

Els u can just do a feature merger with processing duplicates and explode the list on the output.

 

merged are non unique items and not merged are uniques. (u probably need to remove duplicates after this process)

 

 

Gio
D
I am wondering if it would be possible to use the feature merger exploding the list on the output but having a fuzzy string comparer percentage of every record. The question is you can have unreferenced records on your list which differs only in something to the records in your requestor list f.e. "Einstein ST" vs "A Einstein ST" or "41000 Einstein Street", etc

Reply


Community Stats

28,724
Posts
109,519
Replies
36,564
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.