Solved

Tabular data: merge / transpose data based on keyword


Badge

Hi,

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

 

 

 

How can I do this?

 

Thanks,

 

Ed

 

 

icon

Best answer by david_r 20 December 2018, 09:29

View original

12 replies

Userlevel 4

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

Userlevel 4

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)
Badge

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

 

 

 

Userlevel 4

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.

Badge

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

Userlevel 2
Badge +17

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?

Badge

@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
Userlevel 4

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.

Userlevel 2
Badge +17

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.

Badge

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

Userlevel 4

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.

Badge

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

Reply