Question

merge but by group

  • 18 July 2017
  • 7 replies
  • 16 views
K
Badge

Good day,

 

I have two tables with records that I want to join together based on the category attribute:

 

1.table:

 

First attribute is the client, second the category.

 

MAAS CA

 

MAAS IND

 

MAAS SWE

 

BWL CA

 

BWL NO

 

BWL EE

 

 

2.table:

 

First attribute is the cell, second the category.

 

CA345985 CA

 

CA290456 CA

 

IND789345 IND

 

SWE563843 SWE

 

EE621984 EE

 

NO893476 NO

 

EE972086 EE

 

IS856145 IS

 

 

What I would like to have as a result is to create an excel report per client. The report should only contain the cells whose category matches with the client ones in table 1.

 

e.g. MAAS_report.xls containing:

 

CA345985

 

CA290456

 

IND789345

 

SWE563843

 

and BWL_report.xls containing:

 

CA345985

 

CA290456

 

NO893476

 

EE972086

 

This is only a simplified example. I would need to create these reports for hundreds of clients which all should be compared to table 2.

 

Your help is really appreciated.

7 replies

redgeographics
Rank
Userlevel 5
Badge +25

You can use a FeatureMerger to join both datasets on the category attribute, make sure to create a list and then use a ListExploder to turn that into individual features again since there's a one-to-many relationship.

Then you can set a fanout on the Excel writer, using the client attribute as the filename, so for each unique client name you will get a new Excel file.

I've attached a template with this workspace and the sample source data:

none2none.fmwt

ebygomm
Rank
Userlevel 1
Badge +21
Why would BWL report not contain EE621984?
ebygomm
Rank
Userlevel 1
Badge +21

If the above should in fact include EE621984 then I would aggregate your first table, Group By Category, and create a list called CLIENT and join this to the supplier on a featuremerger

The second table would then be joined to the requestor port and merge on the CATEGORY attribute, after merging use a LIST exploder which will create a feature per cell per relevant client

ebygomm
Rank
Userlevel 1
Badge +21

If the above should in fact include EE621984 then I would aggregate your first table, Group By Category, and create a list called CLIENT and join this to the supplier on a featuremerger

The second table would then be joined to the requestor port and merge on the CATEGORY attribute, after merging use a LIST exploder which will create a feature per cell per relevant client

@redgeographics method is neater though :-)

 

redgeographics
Rank
Userlevel 5
Badge +25
@redgeographics method is neater though :-)

 

[takes bow]

 

 

erik_jan
Rank
Userlevel 2
Badge +16

Depending on the source format (and from the word table I take it is a database), you could also use the SQLCreator and have the database do the joining:

Select * from Client_table t1, Cell_table t2 Where t1.client_code = t2.client_code.

Then write the Cell_name and use the Client_name as attribute to name the output file.

takashi
Rank
Userlevel 2
Badge +17

Another thought. The InlineQuerier (SQL statement) could be used here regardless of the source format.

join-tables-with-inlinequerier-example.fmwt (FME 2017.0)

 

 

Reply


Community Stats

28,687
Posts
109,422
Replies
36,543
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.