Question

Denormalize rows into repetitive numbered columns

timotheebecker
Rank
Badge +3

Hi,

I need to denormalize rows into repetitive numbered columns. Here's an example.

Here is the normal version as it is in the relational db. Note that the three rows all have the same value in ID_DETAIL field.

image.pngHere is the desired result. Note that for this one DETAIL, we end up with three groups of columns (ID_MENTION, MENTION, PRECISION).

Denormalized with multiple repetitive columnsHow would one do this in FME ?

The number of repetitive groups of columns would be defined by the detail in the dataset having the most mentions (in my example it was 3).

I was thinking of some kind of grouping on ID_DETAIL and some kind of "for-each-loop". But any better suggestion appreciated !

4 replies

debbiatsafe
Rank
Userlevel 3
Badge +17

Hello @timotheebecker​ 

I would suggest using a Counter to number for each row of a repetitive group and then renaming the attributes based on the resulting number using a BulkAttributeRenamer. Then use an Aggregator to aggregate features with the same ID_DETAIL value into one features. Note that you will need to expose the renamed attributes (eg. ID_MENTION_1, MENTION_1, etc.) using an AttributeExposer.

I have attached a workspace demonstrating this approach.

geomancer
Rank
Userlevel 4
Badge +36

An alternative approach: Build a list (ListBuilder, group by ID_DETAIL), then use ListBreaker (from FME Hub), start numbering at 1. Next use the AttributeExposer.

Denormalize_Rows 

 

timotheebecker
Rank
Badge +3

Hello @timotheebecker​ 

I would suggest using a Counter to number for each row of a repetitive group and then renaming the attributes based on the resulting number using a BulkAttributeRenamer. Then use an Aggregator to aggregate features with the same ID_DETAIL value into one features. Note that you will need to expose the renamed attributes (eg. ID_MENTION_1, MENTION_1, etc.) using an AttributeExposer.

I have attached a workspace demonstrating this approach.

Thanks Debbiatsafe. That works fine!

timotheebecker
Rank
Badge +3

An alternative approach: Build a list (ListBuilder, group by ID_DETAIL), then use ListBreaker (from FME Hub), start numbering at 1. Next use the AttributeExposer.

Denormalize_Rows 

 

Hello,

I haven't tried your approach geomancer but it looks very elegant. I'll try it soon.

Reply


Community Stats

28,731
Posts
109,527
Replies
36,565
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.