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
Solved
ms excel data manipulation
Best answer by fmelizard
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.
+41- Evangelist
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
+19- Contributor
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.
+11- Contributor
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)
then you have to select the column names in Feature Merger settings...If yes then either by Merged port or UnmergedRequestor port you should get features are you getting???
+15- Contributor
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.
+17- Contributor
- Best Answer
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.
rhlkochar wrote:
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.
+19- Contributor
rhlkochar wrote:
And FeatureMerger stops after finding one match. Then it picks up the second ID from column A. How can I get around this problem?
fmelizard wrote:
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.
takashi wrote:
Which version of FME are you using? Did the FeatureMerger reject a feature?
+19- Contributor
rhlkochar wrote:
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
takashi wrote:
Perhaps is this the situation similar to yours?
Join On: ID to FROM_NODE
+19- Contributor
rhlkochar wrote:
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.
+19- Contributor
rhlkochar wrote:
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.
+19- Contributor
rhlkochar wrote:
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.
takashi wrote:
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
Reply
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.