Skip to main content
Solved

Transpose excel data by feature ID

  • April 12, 2024
  • 2 replies
  • 102 views
topotoma
Contributor
Forum|alt.badge.img+3

Hello Community,

I have a question and looked for answers but didn’t find it yet.

It must be simple for someone but I have not yet figured it out. I tried attribute transposer, aggregator etc

I have a table with a feature which has an ID here shown as Feature 1,2,3 and multiple columns with values for each month of the year.

It is like so:

 

What I yould like to get is the following:

In the end I need a row for each column corresponding to the feature it is refering to.

I found some sort of a possibility but it it very “manual” an inelegant in FME as I have to create each month manually and associate a date etc.

 

If someone has a better idea, I am very interested about it. It would help me in the long run as I may have a wrong approach to transposing data with FME.

Many thanks to all the contributors. 

Best regards, Thomas

Best answer by liamfez

I tried replicating your example data set and had some luck with the following method.
I used an AttributeExploder set to list, and keeping attributes. Followed by a ListExploder to explode that new list. Then I used an AttributeKeeper to keep only the ID attribute (POD in your case) and the attr_name and attr_value attributes. I followed this up with a Tester to remove features that were created for attr_name values that started with fme along with the ID.

Input was 3 features with an ID + 3 attributes. Output was 9 features with an ID and 2 attributes.

    This post is closed to further activity.
    It may be an old question, an answered question, an implemented idea, or a notification-only post.
    Please check post dates before relying on any information in a question or answer.
    For follow-up or related questions, please post a new question or idea.
    If there is a genuine update to be made, please contact us and request that the post is reopened.

    2 replies

    liamfez
    Influencer
    Forum|alt.badge.img+44
    • liamfezInfluencer
    • Influencer
    • Best Answer
    • April 12, 2024

    I tried replicating your example data set and had some luck with the following method.
    I used an AttributeExploder set to list, and keeping attributes. Followed by a ListExploder to explode that new list. Then I used an AttributeKeeper to keep only the ID attribute (POD in your case) and the attr_name and attr_value attributes. I followed this up with a Tester to remove features that were created for attr_name values that started with fme along with the ID.

    Input was 3 features with an ID + 3 attributes. Output was 9 features with an ID and 2 attributes.

    Rotate monitor to the right to improve translation speed using gravity
    geomancer
    topotoma
    topotoma
    Contributor
    Forum|alt.badge.img+3
    • topotomaContributor
    • Author
    • Contributor
    • April 12, 2024

    Dear liamfez,

     

    It worked like a charm, thank you very much.

    I had tried the attribute exploder but not with the settings you have indicated, but like this it is perfect.

    Many thanks for your help ! it is greatly appreciated.

    Have a good weekend.

    Best regards,

    Thomas

    Community Stats

    32,154
    Posts
    122,043
    Replies
    39,812
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement
    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.