I have two sets of tables in an excel sheet. I want to pick each element from one column of one of the tables and try to find a match in another column of the other table. As soon as I find a match, I want to merge the corresponding rows. I was trying to use FeatureMerger but wasn't able to perform the task. Could you please help me out? Thank you
Page 1 / 1
Hi @rhlkochar,
I believe the transformer FeatureMerger is good in this case.
The Output port is carry attributes like a list? Have you tried to make the configurations : Attribute Accumulation or Generate List ?
Thanks,
Danilo
Agree that the FeatureMerger would be the way to go. However, I think that the point here is how to read records from two tables within a single worksheet.
If so, please show us a sample data and what you've done so far concretely, which will help us to understand the issue correctly and consider the right way to resolve your issue.
Hi,
I understood that you have 2 excel files and you want to merge rows based on column...
So have you connected to respective ports and provided the settings as below...
one column of one of the tables (Requestor port of Feature Merger)
another column of the other table (Supplier port of Feature Merger)
If yes then either by Merged port or UnmergedRequestor port you should get features are you getting???
records from 2 or more tables in a single worksheet, either on ore more tabs can be controlled by naming the ranges, or referring to starting position for multiple tables on single tab.
Multi tab...seems obvious to me. As would be multiple sheets.
Rest is a vertical lookup. Can be done using featuremerger, llistsearcher, inline querier.
Efficacy woiuld depend on table size and structure.
Hi @rhlkochar, the FME 2018.0 beta currently available (build 18165) has the ability to read in Excel tables similar to Excel sheets. You can specify to read in the tables as different Feature Types which will allow you to perform the merge using the FeatureMerger.
Hi everyone,
I am really sorry for the delayed response. Thank you for your replies. I am attaching a sample dataset here for your reference.
Actually I am looking to match column A (ID) with the From Node in Dataset 2. Say I pick up the first ID from column A. If I get a match in the From Mode, I pick up the corresponding rows of both the matches. Then I move on to the second ID, and so on.
Hi everyone,
Just a small clarification with regard to my last post. I actually want to pick up every ID from column A, one at a time, and check with all the From Node IDs and correspondingly pick up all the rows. I had mentioned that I want the check to stop the moment I found a match. However, I want to find all the matches.
Hi everyone,
Just a small clarification with regard to my last post. I actually want to pick up every ID from column A, one at a time, and check with all the From Node IDs and correspondingly pick up all the rows. I had mentioned that I want the check to stop the moment I found a match. However, I want to find all the matches.
And FeatureMerger stops after finding one match. Then it picks up the second ID from column A. How can I get around this problem?
Hi @rhlkochar, the FME 2018.0 beta currently available (build 18165) has the ability to read in Excel tables similar to Excel sheets. You can specify to read in the tables as different Feature Types which will allow you to perform the merge using the FeatureMerger.
Which version of FME are you using? Did the FeatureMerger reject a feature?
Hi everyone,
Just a small clarification with regard to my last post. I actually want to pick up every ID from column A, one at a time, and check with all the From Node IDs and correspondingly pick up all the rows. I had mentioned that I want the check to stop the moment I found a match. However, I want to find all the matches.
Join On: ID to FROM_NODE
Perhaps is this the situation similar to yours?
Join On: ID to FROM_NODE
Hi everyone,
Just a small clarification with regard to my last post. I actually want to pick up every ID from column A, one at a time, and check with all the From Node IDs and correspondingly pick up all the rows. I had mentioned that I want the check to stop the moment I found a match. However, I want to find all the matches.
As a workaround in the interim, connect a Junction or a Logger to the <Rejected> port.
Hi everyone,
Just a small clarification with regard to my last post. I actually want to pick up every ID from column A, one at a time, and check with all the From Node IDs and correspondingly pick up all the rows. I had mentioned that I want the check to stop the moment I found a match. However, I want to find all the matches.
If you can ignore the second or later duplicate FROM_NODEs, just set Yes to the Process Duplicate Suppliers parameter.
Hi everyone,
Just a small clarification with regard to my last post. I actually want to pick up every ID from column A, one at a time, and check with all the From Node IDs and correspondingly pick up all the rows. I had mentioned that I want the check to stop the moment I found a match. However, I want to find all the matches.
If you need to collect multiple Dataset 2 (FROM_NODE) records matched the same Dataset 1 (ID) record, check the Generate List option in the FeatureMerger to save one or more Dataset 2 features matched the same Dataset 1 into a list attribute.
hi all..
even I am facing the same problem and here is the snapshot of input, output from FME and desire output. and one more if ID does not match it should give for that ID ip1.pngip2.pngoutput-by-featuremarger.pngdesire-output.png