Solved

ms excel data manipulation

R
Badge

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

icon

Best answer by fmelizard 11 September 2017, 18:50

View original

18 replies

danilo_fme
Rank
Userlevel 4
Badge +30

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

takashi
Rank
Userlevel 2
Badge +17

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.

pratap
Rank
Badge +2

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???

G
Badge +3

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.

fmelizard
Rank
Userlevel 4
Badge +13

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.

R
Badge

data.xlsx

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.

 

R
Badge

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.

R
Badge

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?

 

 

takashi
Rank
Userlevel 2
Badge +17
And FeatureMerger stops after finding one match. Then it picks up the second ID from column A. How can I get around this problem?

 

 

Which version of FME are you using? Did the FeatureMerger reject a feature?

 

 

R
Badge

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.

And FeatureMerger stops after finding one match. Then it picks up the second ID from column A. How can I get around this problem? . This is in FME workbench 2017.1

 

 

 

R
Badge
Which version of FME are you using? Did the FeatureMerger reject a feature?

 

 

And FeatureMerger stops after finding one match. Then it picks up the second ID from column A. How can I get around this problem? . This is in FME workbench 2017.1

 

 

 

takashi
Rank
Userlevel 2
Badge +17

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.

Perhaps is this the situation similar to yours?

 

Join On: ID to FROM_NODE

 

 

R
Badge
Perhaps is this the situation similar to yours?

 

Join On: ID to FROM_NODE

 

 

This looks similar. I am understanding the job that AttributeKeeper plays. However, wouldn't FeatureMerger run into the same problems as I mentioned, i.e. stop after finding one match?

 

 

takashi
Rank
Userlevel 2
Badge +17

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.

By default, the rejection would happen when a single Requestor matched two or more Suppliers. However, there aren't duplicate supplier features from your dataset. It could be a potential bug in the FeatureMerger.

 

As a workaround in the interim, connect a Junction or a Logger to the <Rejected> port.

 

takashi
Rank
Userlevel 2
Badge +17

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.

Ah, I found duplicate suppliers! There are two "1229748" FROM_NODEs in the Dataset 2. It wasn't a bug. The FeatureMerger rejects duplicate suppliers by default, even if they don't match any requestor.

 

If you can ignore the second or later duplicate FROM_NODEs, just set Yes to the Process Duplicate Suppliers parameter.

 

takashi
Rank
Userlevel 2
Badge +17

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.

 

R
Badge
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.

 

Thank you so much. I will try your suggestion and get back to you

 

 

J
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


Community Stats

28,670
Posts
109,364
Replies
36,531
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.