Skip to main content
Solved

Tabular data: merge / transpose data based on keyword

E
Forum|alt.badge.img

Hi,

I'm trying to combine two data streams based on keywords. (chain)

 

 

 

How can I do this?

 

Thanks,

 

Ed

 

 

Best answer by david_r

Here's a possible solution using a FeatureMerger to create a category list for each CHAIN element, then a BulkAttributeRenamer to "explode" the list items. You will have to manually expose CAT0...CATn, however, e.g. using an AttributeExposer after the BulkAttributeRenamer. Also note that the CAT attributes start at CAT0, not CAT1.

merge_categories.fmwt

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

    12 replies

    david_r
    Celebrity
    • david_rCelebrity
    • Best Answer
    • December 20, 2018

    Here's a possible solution using a FeatureMerger to create a category list for each CHAIN element, then a BulkAttributeRenamer to "explode" the list items. You will have to manually expose CAT0...CATn, however, e.g. using an AttributeExposer after the BulkAttributeRenamer. Also note that the CAT attributes start at CAT0, not CAT1.

    merge_categories.fmwt

    david_r
    Celebrity
    • david_rCelebrity
    • December 20, 2018
    david_r wrote:

    Here's a possible solution using a FeatureMerger to create a category list for each CHAIN element, then a BulkAttributeRenamer to "explode" the list items. You will have to manually expose CAT0...CATn, however, e.g. using an AttributeExposer after the BulkAttributeRenamer. Also note that the CAT attributes start at CAT0, not CAT1.

    merge_categories.fmwt

    If you really need the CAT attributes to start at 1, you can replace the BulkAttributeRenamer with the following small code in a PythonCaller:

    def ExplodeList(feature):
    items = feature.getAttribute('_categories{}.Category')
    for n, item in enumerate(items):
        feature.setAttribute('CAT%s' % (n+1), item)
    E
    Forum|alt.badge.img
    • edhere
    • Author
    • December 20, 2018
    david_r wrote:

    Here's a possible solution using a FeatureMerger to create a category list for each CHAIN element, then a BulkAttributeRenamer to "explode" the list items. You will have to manually expose CAT0...CATn, however, e.g. using an AttributeExposer after the BulkAttributeRenamer. Also note that the CAT attributes start at CAT0, not CAT1.

    merge_categories.fmwt

    Hi David, thanks for your response.

     

     

    I'm not getting the output I'm looking for:

     

    Attached what I have now (beta 2019) - If you have time, could you please have a look?

    merge_categories2.fmwt

    Thanks,

     

    Ed

     

     

     

    david_r
    Celebrity
    • david_rCelebrity
    • December 20, 2018
    edhere wrote:

    Hi David, thanks for your response.

     

     

    I'm not getting the output I'm looking for:

     

    Attached what I have now (beta 2019) - If you have time, could you please have a look?

    merge_categories2.fmwt

    Thanks,

     

    Ed

     

     

     

    Could you please re-upload the template with the accompanying Excel files? That'll make it a lot easier to test.

    E
    Forum|alt.badge.img
    • edhere
    • Author
    • December 20, 2018
    david_r wrote:

    Could you please re-upload the template with the accompanying Excel files? That'll make it a lot easier to test.

    merge_categories2.fmwtInput_categories.xlsxInput_chains.xlsxMmm, I thought a template FME file contains all related files. Anyways, I've attached all files. Thanks

    takashi
    Evangelist
    • takashiEvangelist
    • December 20, 2018
    david_r wrote:

    Could you please re-upload the template with the accompanying Excel files? That'll make it a lot easier to test.

    I was able to get desired result from your workspace, with FME 2018.1.1.1.

    There could be issues in the Beta. Why not use a release version?

    E
    Forum|alt.badge.img
    • edhere
    • Author
    • December 20, 2018

    @david_r / @takashi

    Thanks gentlemen, you're right, it works in 2018.

     

    I started using the 2019 beta when I encountered some crashed with the 2018 version :-( Guess I have to switch back!

     

     

    Cheers,

     

    Ed
    david_r
    Celebrity
    • david_rCelebrity
    • December 20, 2018
    edhere wrote:

    merge_categories2.fmwtInput_categories.xlsxInput_chains.xlsxMmm, I thought a template FME file contains all related files. Anyways, I've attached all files. Thanks

    Seems to be an issue with the BulkAttributeRenamer in the 2019 Beta. You should consider signalling it to Safe.

    But I agree with Takashi, why use a beta version at all. Unless you have a very specific reason I would actually recommend against it.

    If you really need to use the 2019 Beta, consider replacing the BulkAttributeRenamer with the PythonCaller and the code I posted above, it should work.

    takashi
    Evangelist
    • takashiEvangelist
    • December 20, 2018
    edhere wrote:

    Hi David, thanks for your response.

     

     

    I'm not getting the output I'm looking for:

     

    Attached what I have now (beta 2019) - If you have time, could you please have a look?

    merge_categories2.fmwt

    Thanks,

     

    Ed

     

     

     

    Good to hear you got desired result.

    This is another approach. I don't think merging two tables is essential in this case. FYI.

    E
    Forum|alt.badge.img
    • edhere
    • Author
    • December 20, 2018
    david_r wrote:

    If you really need the CAT attributes to start at 1, you can replace the BulkAttributeRenamer with the following small code in a PythonCaller:

    def ExplodeList(feature):
    items = feature.getAttribute('_categories{}.Category')
    for n, item in enumerate(items):
        feature.setAttribute('CAT%s' % (n+1), item)

    Apologies David, my Python FME knowledge is non existent.

     

    Where do I paste this piece of code in the PythonCaller? (I mean what parts of code in the PythonCaller should I keep?)

     

    What should all the settings look like?

    Thank you

    david_r
    Celebrity
    • david_rCelebrity
    • December 20, 2018
    edhere wrote:

    Apologies David, my Python FME knowledge is non existent.

     

    Where do I paste this piece of code in the PythonCaller? (I mean what parts of code in the PythonCaller should I keep?)

     

    What should all the settings look like?

    Thank you

    It should look like this:

    There should be none of the template code, only the code you copy/paste from the posting above.

    farfar
    Contributor
    Forum|alt.badge.img+11
    • farfarContributor
    • Contributor
    • December 20, 2018

    I tested a David solution with 2017.1 version and it works also.

    Reply


    Helpful Members This Week

    Community Stats

    31,504
    Posts
    119,422
    Replies
    39,122
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement

    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.