Question

Denormalization use case

  • 12 September 2022
  • 4 replies
  • 7 views
timotheebecker
Rank
Badge +3

Hi there,

I have a database containing a BUILDING table which is related to a BUILDING_FUNCTION table.

A building can have 0-n functions associated to it (i.e. Schools, Public buildings, etc).

But it's more complicated than that. The functions are timely (Origin, Intermediary, Present)

And there are three levels of details in the functions (I-basic, II-medium, III-detail)

Here is an example simplied dataset for only one building, number 14...

Example function dataI need to denormalize this table to get only one entry per building.

Here is the desired result.

Example output functions 

Note that in INTERMEDIARY functions, there are two functions listed. This is due to the fact that the a building can have more than one set of functions for each timely period. In the example above, where the TIME attribute has "2 Intermediary", you have two sets of basic-medium-detail FUNCTION_LEVEL.

 

I'm joining an excel spreadsheet as sample data.

Thanks a ton in advance for your help !

 

PS. This is somewhat related to my previous post but the added complexity is puzzling me. https://community.safe.com/s/question/0D54Q00009X7KroSAF/denormalize-rows-into-repetitive-numbered-columns

1 Attachment

4 replies

M
Badge +2

@timotheebecker​ Try the Aggregator transformer. Use Group By for the Building ID and then Attribute to Concatenate

timotheebecker
Rank
Badge +3

Thank you very much @Mark Stoakes​ !

As it seems a little complex, I've tried simplifying the problem to move forward. But there's still something I'm not able to do.

I try treating the cases grouped by TIME. So for 1 ORIGIN, the columns to create are as follow :

imageAnd for INTERMEDIATE, it would be the same columns but replacing ORIGIN by INTERMEDIATE.

 

As there are two sets of values for INTERMEDIATE, I need concatenation. Whild my test works without concatenation, it doesn't work when concatenation is needed.

Here attached is my workbench and sample data. Could you have a look please ?

Cheers,

Tim

1 Attachment
M
Badge +2

@timotheebecker​ Not quite a transpose problem. I'm not sure if the attached gives you exactly what you need, but hopefully it gives you some ideas. I'm sure there is a more elegant approach - perhaps a simple Python script.

timotheebecker
Rank
Badge +3

Thank you !

That works a charm !

For others interested, the key transformers here are

  • AttributeFilter
  • Aggregator
  • AttributeRenamer

Here attached is my final testcase.

And here's a screenshot of it.

imageThanks a ton ! @Mark Stoakes​ 

Reply


Community Stats

28,630
Posts
109,152
Replies
36,507
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.