Skip to main content

I have two tables that form a child parent relationship. The parent record has a primary key and some field data, and the child record has a foreign key and some field data. I am trying to append each child record's field data onto the end of each parent record. There can be anywhere between 1 and 99 child records for every parent record, and the field names from the child record should be repeating.

 

I have attached some sample data to show what I am trying to achieve. The spreadsheet contains a parent table, a child table, and a final table showing the desired final format.

 

Parent:

Parent 

Child:

Child 

Final:

Final 

It basically look something like this:

 

Parent Table:

Parent-Field 1 (Key) | Parent-Field 2

 

Child Table:

Child-Field 1 (F-Key) | Child-Field 2 | Child-Field 3

 

Final Table:

Parent-Field 1 (Key) | Parent-Field 2 | Child-Field 2 {0} | Child-Field 3 {0} | Child-Field 2 {n} | Child-Field 3 {n}

 

 

I have tried using aggregators, feature joiners, database joiners, aggregators, list builders, and splitters, but I can't seem to get it working. The data is freeform, so I want to try and stay away from concatenations if possible.

 

Again, this is a simplified version of the data I am looking at translating. To do this process manually takes several hours, and consists of thousands of records between the parent and child tables.

Hi @redlands_gis​ 

Your desired output is the opposite of the one described in this Community question but I think the same method can be applied. I've attached a workspace demonstrating this approach.

joinRowsToParentUnfortunately, the dynamically created output fields are sorted alphabetically (ie. Grade_1, Grade_2, Name_1, Name 2 ). But you should be able to use a dynamic writer with a schema feature to specify the output attribute sort order.


Hi @redlands_gis​ 

Your desired output is the opposite of the one described in this Community question but I think the same method can be applied. I've attached a workspace demonstrating this approach.

joinRowsToParentUnfortunately, the dynamically created output fields are sorted alphabetically (ie. Grade_1, Grade_2, Name_1, Name 2 ). But you should be able to use a dynamic writer with a schema feature to specify the output attribute sort order.

Hi @debbiatsafe​ 

 

Thank you for the quick reply. I will have to test this method. From what I can tell, this process is assigning an index number to each child record and then creating attribute names with the appended index number. When recombining the lists, you'll be left with repeating sets of attributes, all with their own unique attribute number. If it does work that way, I will have to clean up the field names to not have index numbers, but I think that should be fairly easy to do.


Hi @debbiatsafe​ 

 

Thank you for the quick reply. I will have to test this method. From what I can tell, this process is assigning an index number to each child record and then creating attribute names with the appended index number. When recombining the lists, you'll be left with repeating sets of attributes, all with their own unique attribute number. If it does work that way, I will have to clean up the field names to not have index numbers, but I think that should be fairly easy to do.

Hi @redlands_gis​ 

You're very welcome! You may run into issues after removing the index numbers from the field names. FME cannot write multiple attributes with the same name as shown in the Final Table worksheet.

What is the output format? Depending on the format (eg. CSV or Excel), there may be ways to work around this.


Hi @debbiatsafe​ 

 

Thank you for the quick reply. I will have to test this method. From what I can tell, this process is assigning an index number to each child record and then creating attribute names with the appended index number. When recombining the lists, you'll be left with repeating sets of attributes, all with their own unique attribute number. If it does work that way, I will have to clean up the field names to not have index numbers, but I think that should be fairly easy to do.

Hi @debbiatsafe​ 

The export format will be a .XLSX file.

 

I am trying to step through each of the transposers in your model, and I can't seem to understand what's happening in the BulkAttributeKeeper. It is just an expression, and I am not sure what it's doing.

 

This is the expression:

(\\d+$|^Relate)


If you want to write the same column names to Excel. Which I would nearly never recommend... but it is possible. You can write to column_names (A, B, C etc. ) and row_numbers (1,2,3 etc). First create a feature that has all the desired column names:

 

AttributeCreator:

Name - Value

A - Record

B - Date

C - Name

D - Grade

E - Name

F - Grade

xlsx_row_number - 1

 

Then process all your features. A01 should be in Attribute Name A. The B- grade of your example should be in the Attribute Name D. And the feature should have an Attribute xlsx_row_number set to 2.

 

The Aggregator should merge all the features to the right rows.

 

 

Or do it all in Excel without FME:

You should use VLOOKUP in Excel to merge the Date to the Record.

Then follow the steps in this tutorial:

https://www.danbp.org/p/en/node/127

 

I added the Pure Excel result as an attachment.

 

 

 

 


If you want to write the same column names to Excel. Which I would nearly never recommend... but it is possible. You can write to column_names (A, B, C etc. ) and row_numbers (1,2,3 etc). First create a feature that has all the desired column names:

 

AttributeCreator:

Name - Value

A - Record

B - Date

C - Name

D - Grade

E - Name

F - Grade

xlsx_row_number - 1

 

Then process all your features. A01 should be in Attribute Name A. The B- grade of your example should be in the Attribute Name D. And the feature should have an Attribute xlsx_row_number set to 2.

 

The Aggregator should merge all the features to the right rows.

 

 

Or do it all in Excel without FME:

You should use VLOOKUP in Excel to merge the Date to the Record.

Then follow the steps in this tutorial:

https://www.danbp.org/p/en/node/127

 

I added the Pure Excel result as an attachment.

 

 

 

 

Hi @jkr_da​ 

 

Yeah, I'm not a fan of this schema, but it's the format that is need to do an import to a specific system. I would have just used a child and parent table myself.


Reply