I have attached an excel spreedsheet. I would like to merge and convert the table in sheet 1 ("Year_2035') and sheet 2 ('Year_2050') into a table structure like that one in 'RequiredFormat' sheet. I have never transformed such a complex data structure in FME before. Can anyone kindly help with good suggestions?
Question
How to transform wide table with multi level nested columns and multi-rows into a longer format?
+22- Safer
Hello @smfks911
It sounds like you want to transpose the Excel data. It looks like the inpu t Excel sheets are fixed forms. If they are fixed, you should be able to use a modified version of Example 2 workflow in the transpose article linked above where an AttributeExploder is used to explode only columns containing data (eg. columns C-F) in your sample dataset.
Once these data features have been exploded, you can add attribute values based on the original column name. For example, data from column C would contain the following attributes and values: DevelopmentType=All, Ownership Type=Private,Land Type=Open space.
I have attached a sample workspace demonstrating this workflow.
Hello @smfks911
It sounds like you want to transpose the Excel data. It looks like the inpu t Excel sheets are fixed forms. If they are fixed, you should be able to use a modified version of Example 2 workflow in the transpose article linked above where an AttributeExploder is used to explode only columns containing data (eg. columns C-F) in your sample dataset.
Once these data features have been exploded, you can add attribute values based on the original column name. For example, data from column C would contain the following attributes and values: DevelopmentType=All, Ownership Type=Private,Land Type=Open space.
I have attached a sample workspace demonstrating this workflow.
Thank you @debbiatsafe for your reply and providing with a sample workspace. That is really useful to understand the workflow.
Login to the community
No account yet? Create an account
An FME Account is required to contribute
LoginEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.