Skip to main content
Solved

Transpose excel data by feature ID

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.

View original
    Did this help you find an answer to your question?

    2 replies

    liamfez
    Influencer
    Forum|alt.badge.img+34
    • 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.

    not a bot
    geomancer
    topotoma
    2 people upvoted this
    • geomancer
      geomancer
    • topotoma
      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

    Reply


    Helpful Members This Week

    Community Stats

    31,048
    Posts
    117,707
    Replies
    38,825
    Members

    Latest FME

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie 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.